Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 11 hours 36 min ago

RMAN MAXSETSIZE, MAXPIECESIZE, FILESPERSET Unveil

Thu, 2016-12-15 20:14

First, any backup implementation should have recovery point and time objective.

Next, there’s no right or wrong, just what fits the requirements.

FILESPERSET controls maximum number of files for each backupset.
MAXPIECESIZE controls maximum size of backuppiece.
MAXSETSIZE controls maximum size of backupset.

Typically, if MAXSETSIZE is set, it should be equal to or greater than maximum size of all data files.
In general, it is not recommended to set MAXSETSIZE.

Backupset can contains 1 or more backuppiece; hence, why set a limitation on maxsetsize when it may be better to set limitation for maxpiecsize?

Justification for filesperset=1 is faster recovery and filesperset>1 is to reduce management of backup, e.g. crosscheck.

Consideration, backup versus restore frequencies.

Here’s an analogy, you are at bank withdrawing $2000 and the teller ask, how do you want the bill?
Would you like 20 $100 bills or multiple small bills 1,5,10,20 or combination of there of?

Same concept applies to backupset and filesperset.

Note: FILESPERSET and MAXOPENFILES affect multiplexing, number of buffers, size of each buffer and is not the scope of this blog post.

From the DEMO, there is only 1 backuppiece for each backupset and depending on maxsetsize, the number of backupsets created is different.

MAX DATAFILE SIZE IS 513M

ARROW1:(SYS@HAWKA):PRIMARY> select max(bytes)/1024/1024 from v$datafile;

MAX(BYTES)/1024/1024
--------------------
                 513

ARROW1:(SYS@HAWKA):PRIMARY> select name, bytes/1024/1024 from v$datafile;

NAME                                                    BYTES/1024/1024
------------------------------------------------------- ---------------
/oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf                  513
/oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf                  257
/oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf                 256
/oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf                   129
/oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf                    16

ARROW1:(SYS@HAWKA):PRIMARY>

Notice the distribution and size of datafiles in backupsets.

CONFIGURE MAXSETSIZE TO UNLIMITED

PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
4 BACKUPSETS CREATED FOR LEVEL0 (2 datafiles, 1 controlfile, 1 spfile)
4 BACKUPSETS CREATED FOR LEVEL1
8 BACKUPSETS TOTAL SIZE 413680k
---------------------------------------------------------------------------
Datafiles backupset 1 (181.24M)
  2    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf (257M)
  3    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf(256M)
  4    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf  (129M)

NOTICE: sum of datafiles > 513M since maxsetsize is unlimited and backuppiece < 513M
Datafiles backupset 2 (190.24M)
  1    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf (513M)
  5    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf  ( 16M)

CONFIGURE MAXSETSIZE TO 513 M

PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
5 BACKUPSETS CREATED FOR LEVEL0 (3 datafiles, 1 controlfile, 1 spfile)
5 BACKUPSETS CREATED FOR LEVEL1
10 BACKUPSETS TOTAL SIZE 413808k
---------------------------------------------------------------------------
NOTICE: backupset is only 3.59M and no other datafiles can be considered since it would exceed maxsetsize of 513M
Datafiles backupset 1 (3.59M) 
  3    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf (256M)
  4    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf   (129M)

Before backup, RMAN does not know datafile usage and it would be too time consumsing to check. Only datafile size is checked.
RMAN performed NULL compression since backupset is 189M and datafile is 513M.

NOTICE: sum of datafiles = 513M, maxsetsize is 513M, backuppiece < 513M
Datafiles backupset 2 (189.23M)
  1    0  Incr 1706518    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf  (513M)

Datafiles backupset 3 (178.68M)
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf  (257M)
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf   ( 16M)

All the details.

CONFIGURE MAXSETSIZE TO UNLIMITED

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/backup/%d_%I_%T_%U.bkp' MAXPIECESIZE 513 M MAXOPENFILES 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKA.f'; # default

RMAN> backup incremental level 0 database filesperset 4 tag 'L0_MAX';
RMAN> list backup summary tag L0_MAX;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
213     B  0  A DISK        2016-DEC-14 19:38:43 1       1       NO         L0_MAX
214     B  0  A DISK        2016-DEC-14 19:38:43 1       1       NO         L0_MAX
215     B  0  A DISK        2016-DEC-14 19:38:45 1       1       NO         L0_MAX
216     B  0  A DISK        2016-DEC-14 19:38:45 1       1       NO         L0_MAX

RMAN> list backup tag L0_MAX;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
213     Incr 0  181.24M    DISK        00:00:02     2016-DEC-14 19:38:43
        BP Key: 243   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6prnfjq1_1_1.bkp
  List of Datafiles in backup set 213
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf 
  3    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf  
  
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
214     Incr 0  190.24M    DISK        00:00:02     2016-DEC-14 19:38:43
        BP Key: 244   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6qrnfjq1_1_1.bkp
  List of Datafiles in backup set 214
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf
  5    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
215     Incr 0  80.00K     DISK        00:00:00     2016-DEC-14 19:38:45
        BP Key: 245   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6srnfjq5_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
216     Incr 0  16.08M     DISK        00:00:00     2016-DEC-14 19:38:45
        BP Key: 246   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6rrnfjq5_1_1.bkp
  Control File Included: Ckp SCN: 1706149      Ckp time: 2016-DEC-14 19:38:45

RMAN> backup incremental level 1 database filesperset 8 tag 'L1_MAX';
RMAN> list backup summary tag L1_MAX;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
217     B  1  A DISK        2016-DEC-14 19:39:14 1       1       NO         L1_MAX
218     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX
219     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX
220     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX

RMAN> list backup tag L1_MAX;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
217     Incr 1  40.00K     DISK        00:00:02     2016-DEC-14 19:39:14
        BP Key: 247   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6urnfjr0_1_1.bkp
  List of Datafiles in backup set 217
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    1  Incr 1706213    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf
  5    1  Incr 1706213    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
218     Incr 1  48.00K     DISK        00:00:03     2016-DEC-14 19:39:15
        BP Key: 248   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6trnfjr0_1_1.bkp
  List of Datafiles in backup set 218
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  3    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
219     Incr 1  80.00K     DISK        00:00:00     2016-DEC-14 19:39:15
        BP Key: 249   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_70rnfjr3_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
220     Incr 1  16.08M     DISK        00:00:00     2016-DEC-14 19:39:15
        BP Key: 250   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6vrnfjr3_1_1.bkp
  Control File Included: Ckp SCN: 1706215      Ckp time: 2016-DEC-14 19:39:15

RMAN> exit

oracle@arrow1:HAWKA:/home/oracle
$ du -skc /oradata/backup/HAWK_3187737370_20161214*|sort -n
48      /oradata/backup/HAWK_3187737370_20161214_6urnfjr0_1_1.bkp
56      /oradata/backup/HAWK_3187737370_20161214_6trnfjr0_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_6srnfjq5_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_70rnfjr3_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_6rrnfjq5_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_6vrnfjr3_1_1.bkp
185604  /oradata/backup/HAWK_3187737370_20161214_6prnfjq1_1_1.bkp 
194820  /oradata/backup/HAWK_3187737370_20161214_6qrnfjq1_1_1.bkp
413680  total
oracle@arrow1:HAWKA:/home/oracle
$

MAX DATAFILE SIZE IS 513M AND CONFIGURE MAXSETSIZE TO 513 M

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/backup/%d_%I_%T_%U.bkp' MAXPIECESIZE 513 M MAXOPENFILES 1;
CONFIGURE MAXSETSIZE TO 513 M;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKA.f'; # default

RMAN> backup incremental level 0 database filesperset 4 tag 'L0_513';
RMAN> list backup summary tag L0_513;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
221     B  0  A DISK        2016-DEC-14 19:42:36 1       1       NO         L0_513
222     B  0  A DISK        2016-DEC-14 19:42:37 1       1       NO         L0_513
223     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513
224     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513
225     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513

RMAN> list backup tag L0_513;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
221     Incr 0  3.59M      DISK        00:00:00     2016-DEC-14 19:42:36
        BP Key: 251   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_72rnfk1c_1_1.bkp
  List of Datafiles in backup set 221
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
222     Incr 0  189.23M    DISK        00:00:01     2016-DEC-14 19:42:37
        BP Key: 252   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_71rnfk1c_1_1.bkp
  List of Datafiles in backup set 222
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1706518    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
223     Incr 0  178.68M    DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 253   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
  List of Datafiles in backup set 223
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
224     Incr 0  80.00K     DISK        00:00:00     2016-DEC-14 19:42:38
        BP Key: 254   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_75rnfk1e_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
225     Incr 0  16.08M     DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 255   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_74rnfk1d_1_1.bkp
  Control File Included: Ckp SCN: 1706522      Ckp time: 2016-DEC-14 19:42:37

RMAN> backup incremental level 1 database filesperset 8 tag 'L1_513';
RMAN> list backup summary tag L1_513;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
226     B  1  A DISK        2016-DEC-14 19:44:20 1       1       NO         L1_513
227     B  1  A DISK        2016-DEC-14 19:44:20 1       1       NO         L1_513
228     B  1  A DISK        2016-DEC-14 19:44:22 1       1       NO         L1_513
229     B  1  A DISK        2016-DEC-14 19:44:23 1       1       NO         L1_513
230     B  1  A DISK        2016-DEC-14 19:44:23 1       1       NO         L1_513

RMAN> list backup tag L1_513;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
226     Incr 1  56.00K     DISK        00:00:01     2016-DEC-14 19:44:20
        BP Key: 256   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_77rnfk4j_1_1.bkp
  List of Datafiles in backup set 226
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    1  Incr 1706668    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    1  Incr 1706668    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
227     Incr 1  32.00K     DISK        00:00:01     2016-DEC-14 19:44:20
        BP Key: 257   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_76rnfk4j_1_1.bkp
  List of Datafiles in backup set 227
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    1  Incr 1706667    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
228     Incr 1  96.00K     DISK        00:00:00     2016-DEC-14 19:44:22
        BP Key: 258   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_78rnfk4m_1_1.bkp
  List of Datafiles in backup set 228
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    1  Incr 1706671    2016-DEC-14 19:44:22 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    1  Incr 1706671    2016-DEC-14 19:44:22 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
229     Incr 1  80.00K     DISK        00:00:00     2016-DEC-14 19:44:23
        BP Key: 259   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_7arnfk4n_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
230     Incr 1  16.08M     DISK        00:00:01     2016-DEC-14 19:44:23
        BP Key: 260   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_79rnfk4m_1_1.bkp
  Control File Included: Ckp SCN: 1706671      Ckp time: 2016-DEC-14 19:44:22

RMAN> list backuppiece '/oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp';

using target database control file instead of recovery catalog

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
253     223     1   1   AVAILABLE   DISK        /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp

RMAN> list backupset 223;

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
223     Incr 0  178.68M    DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 253   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
  List of Datafiles in backup set 223
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

RMAN>

$ du -skc /oradata/backup/HAWK_3187737370_20161214*|sort -n
40      /oradata/backup/HAWK_3187737370_20161214_76rnfk4j_1_1.bkp
64      /oradata/backup/HAWK_3187737370_20161214_77rnfk4j_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_75rnfk1e_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_7arnfk4n_1_1.bkp
104     /oradata/backup/HAWK_3187737370_20161214_78rnfk4m_1_1.bkp
3688    /oradata/backup/HAWK_3187737370_20161214_72rnfk1c_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_74rnfk1d_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_79rnfk4m_1_1.bkp
182976  /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
193784  /oradata/backup/HAWK_3187737370_20161214_71rnfk1c_1_1.bkp
413808  total

Linux locate/print block device attributes ASMLib

Tue, 2016-12-13 08:38

Just learned about this specifically to identify whether disk is being used by ASMLib

blkid – command-line utility to locate/print block device attributes

Oracle Linux Server release 6.4
[root@rac02:/root]
# ll /etc/*release*
-rw-r--r--. 1 root root 32 Feb 22  2013 /etc/oracle-release
-rw-r--r--. 1 root root 55 Feb 22  2013 /etc/redhat-release
lrwxrwxrwx. 1 root root 14 Nov 29  2014 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root 45 Feb 22  2013 /etc/system-release-cpe

[root@rac02:/root]
# cat /etc/system-release
Oracle Linux Server release 6.4

[root@rac02:/root]
# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)

[root@rac02:/root]
# /sbin/blkid |sort
/dev/mapper/vg01-lv_root: UUID="97968448-9997-42a0-a106-c438a47345b8" TYPE="ext4"
/dev/mapper/vg01-lv_swap: UUID="8fe8b719-a367-4448-9ae2-76b376ad91d5" TYPE="swap"
/dev/sda1: UUID="33162370-b7a1-45ae-9c8c-966b8bd720e3" TYPE="ext4"
/dev/sda2: UUID="qHuGcf-Ntnr-hoLR-qtEb-cdgz-2sph-jgaKDK" TYPE="LVM2_member"
/dev/sdb1: LABEL="DISK1" TYPE="oracleasm"
/dev/sdc1: LABEL="DISK2" TYPE="oracleasm"
/dev/sdd1: LABEL="DISK3" TYPE="oracleasm"
/dev/sde1: LABEL="DISK4" TYPE="oracleasm"
/dev/sdf1: LABEL="DISK5" TYPE="oracleasm"
/dev/sdg1: LABEL="DISK6" TYPE="oracleasm"
/dev/sdh1: LABEL="DISK7" TYPE="oracleasm"
/dev/sdi1: LABEL="DISK8" TYPE="oracleasm"
Oracle Linux Server release 6.6
[root@arrow1 ~]# ll /etc/*release*
-rw-r--r--. 1 root root 32 Oct 15  2014 /etc/oracle-release
-rw-r--r--. 1 root root 55 Oct 15  2014 /etc/redhat-release
lrwxrwxrwx. 1 root root 14 Jun 24  2015 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root 45 Oct 15  2014 /etc/system-release-cpe

[root@arrow1 ~]# cat /etc/system-release
Oracle Linux Server release 6.6

[root@arrow1 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6 (Santiago)

[root@arrow1 ~]# /sbin/blkid
/dev/sda1: UUID="7c42cea0-f23d-4fec-ad0f-b1bf4b50b17e" TYPE="ext4"
/dev/sda2: UUID="ZVWdTj-8EQN-F3ac-3Tev-xTsb-ssL8-7euz5X" TYPE="LVM2_member"
/dev/sdb1: UUID="KUEy1I-X5i2-CuSm-krTA-R5K5-i4je-Ek56ZK" TYPE="LVM2_member"
/dev/mapper/vg01-LogVol01: UUID="8e2c236c-dd87-4be6-9eaf-f72f32f0dcc6" TYPE="ext4"
/dev/mapper/vg01-LogVol00: UUID="919bab13-82b6-425b-95c8-87975cb0bf20" TYPE="swap"
Oracle Linux Server release 7.3
[root@owl ~]# ll /etc/*release*
-rw-r--r--. 1 root root  32 Nov  7 22:07 /etc/oracle-release
-rw-r--r--. 1 root root 398 Nov  7 22:07 /etc/os-release --- (New as of OEL7?)
-rw-r--r--. 1 root root  52 Nov  7 22:07 /etc/redhat-release
lrwxrwxrwx. 1 root root  14 Dec 12 23:31 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root  31 Nov  7 22:07 /etc/system-release-cpe

[root@owl ~]# cat /etc/system-release
Oracle Linux Server release 7.3

[root@owl ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)

[root@owl ~]# cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.3"
ID="ol"
VERSION_ID="7.3"
PRETTY_NAME="Oracle Linux Server 7.3"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:3:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.3
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.3

[root@owl ~]# /sbin/blkid
/dev/sr0: UUID="2016-11-21-16-51-51-00" LABEL="VBOXADDITIONS_5.1.10_112026" TYPE="iso9660"
/dev/sda1: UUID="65bf9c73-fb56-49c3-b55e-85de8f318892" TYPE="xfs"
/dev/sda2: UUID="p4rqW2-uzvr-6DpX-nof0-7tgf-Yfvn-n8ehaB" TYPE="LVM2_member"
/dev/mapper/vg01-root: UUID="de9c65d9-60cc-45b8-b9fa-7459ef3f3850" TYPE="xfs"
/dev/mapper/vg01-swap: UUID="1f85c253-261e-4021-9468-651de69b8e7a" TYPE="swap"
[root@owl ~]#

GoldenGate Capture using Active DataGuard

Thu, 2016-12-08 21:38

How to Configure Extract on Standalone Active Data Guard System if Primary is RAC Multipe Nodes (Doc ID 1962336.1)

Configuring Classic Capture in Oracle Active Data Guard Only Mode
http://docs.oracle.com/goldengate/1212/gg-winux/GIORA/classic_capture.htm#GIORA997


dgmgrl from OS command line

Thu, 2016-12-08 21:32

Quick and dirty post from what I just learned.

Who said you can’t teach old dogs new tricks!

oracle@arrow1:HAWKA:/home/oracle
$ dgmgrl / "show database hawka"
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    HAWKA

Database Status:
SUCCESS

oracle@arrow1:HAWKA:/home/oracle
$ dgmgrl / "show database hawkb"
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 2 seconds ago)
  Apply Rate:      1.11 MByte/s
  Real Time Query: ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

oracle@arrow1:HAWKA:/home/oracle
$

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

Wed, 2016-11-30 07:37

This is a quick and dirty note to self about Doc ID 1387859.1 which may be incorrect for Single Instance Database with Oracle Restart.

Please share if you have configuration for Single Instance Database with Oracle Restart using DataGuard Broker and listener registered from Grid – TIA.

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)	

+++ DO NOT BELIEVE THIS TO BE CORRECT - FAILED FOR TEST CASE.

Single Instance Database with Oracle Restart

Here there is no cluster, but clusterware has been installed to enable the Oracle Restart feature. 

The local listener LISTENER has its LISTENER.ORA located in the /network/admin directory of the Oracle Grid Infrastructure home. 

In this case the static service registration is:
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)
     (SID_NAME=sid_name)
    )
  )

As with Single Instance databases, the SID_NAME value sid_name will default to the db_unique_name.

+++ SUCCESS
Data Guard & Oracle Restart in 11gR2 https://uhesse.com/2010/09/06/data-guard-oracle-restart-in-11gr2/

DEMO: Listener configured from DB Home and NOT GI Home which failed.

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ ll
total 0
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:13 alert_HAWKB.log
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:13 drcHAWKB.log
oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ srvctl config listener -l listener11g
Name: LISTENER11G
Home: /u01/app/oracle/product/11.2.0.4/db_1
End points: TCP:1551

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ lsnrctl status listener11g

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2016 05:14:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-NOV-2016 05:03:54
Uptime                    0 days 0 hr. 10 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow2.localdomain)(PORT=1551)))
Services Summary...
Service "HAWKB.localdomain" has 2 instance(s).
  Instance "HAWKB", status UNKNOWN, has 1 handler(s) for this service...
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWKB_DGB.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWKB_DGMGRL.localdomain" has 1 instance(s).
  Instance "HAWKB", status UNKNOWN, has 1 handler(s) for this service...
Service "HAWKB_SVC.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWK_SVC.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow2                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow2                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow2                   STABLE
ora.hawkb.db
      1        ONLINE  ONLINE       arrow2                   Open,STABLE
ora.hawkb.hawk_svc.svc
      1        ONLINE  ONLINE       arrow2                   STABLE
--------------------------------------------------------------------------------

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ dgmgrl sys/oracle@hawka
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawkb - Primary database
    hawka - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawkb

Database - hawkb

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

DGMGRL> show database hawka

Database - hawka

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    HAWKA

Database Status:
SUCCESS

DGMGRL> switchover to hawka
Performing switchover NOW, please wait...
New primary database "hawka" is opening...
Operation requires startup of instance "HAWKB" on database "hawkb"
Starting instance "HAWKB"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawka"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawka - Primary database
    hawkb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawka

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    HAWKA

Database Status:
SUCCESS

DGMGRL> show database hawkb

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

DGMGRL> exit

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow2                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow2                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow2                   STABLE
ora.hawkb.db
      1        ONLINE  ONLINE       arrow2                   Open,Readonly,STABLE
ora.hawkb.hawk_svc.svc
      1        ONLINE  ONLINE       arrow2                   STABLE
--------------------------------------------------------------------------------
oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$

++++++++++

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ ll
total 0
-rw-r--r--. 1 oracle oinstall 0 Nov 30 05:12 alert_HAWKA.log
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:12 drcHAWKA.log

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ srvctl config listener -l listener11g
Name: LISTENER11G
Home: /u01/app/oracle/product/11.2.0.4/db_1
End points: TCP:1551

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ lsnrctl status listener11g

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2016 05:14:39

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-NOV-2016 05:04:43
Uptime                    0 days 0 hr. 9 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow1.localdomain)(PORT=1551)))
Services Summary...
Service "HAWKA.localdomain" has 2 instance(s).
  Instance "HAWKA", status UNKNOWN, has 1 handler(s) for this service...
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWKA_DGB.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWKA_DGMGRL.localdomain" has 1 instance(s).
  Instance "HAWKA", status UNKNOWN, has 1 handler(s) for this service...
Service "HAWKA_SVC.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWK_SVC.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow1                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow1                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow1                   STABLE
ora.hawka.db
      1        ONLINE  ONLINE       arrow1                   Open,Readonly,STABLE
ora.hawka.hawk_svc.svc
      1        ONLINE  ONLINE       arrow1                   STABLE
--------------------------------------------------------------------------------
oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow1                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow1                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow1                   STABLE
ora.hawka.db
      1        ONLINE  ONLINE       arrow1                   Open,STABLE
ora.hawka.hawk_svc.svc
      1        ONLINE  ONLINE       arrow1                   STABLE
--------------------------------------------------------------------------------
oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$


Online Redo Log Switching from RMAN Backup

Sat, 2016-11-26 11:28

I was troubleshooting backup from standby databases and encountered an oddity which I wanted to verify.

Backing Up Archived Redo Logs with RMAN

Before beginning the backup, RMAN switches out of the current redo log group, and archives all online redo logs that have not yet been archived, up to and including the redo log group that was current when BACKUP command with any of the following clauses:
PLUS ARCHIVELOG
ARCHIVELOG ALL
ARCHIVELOG FROM …

 

 


VirtualBox OEL 7.3 Minimal Install for 12c

Wed, 2016-11-23 21:47

Finally, I was able to configure OEL 7.3 Minimal Install.

OEL 7.3 was a challenge since NAT and Host Only Networking from VirtualBox was not working.

I broke down and ended up using Bridged Adapter.

Next, blogs out there were using Server with GUI installation which is a really bloated option.

Even tried CentOS 7.0 Gnome and did not like having to install UEK or rebuild RPM.

Did it work?

VirtualBox OEL 7.3 Minimal Install for 12c

Silent Install CRS_SWONLY 12c using OEL 7.3 Minimal Install

Will you try it and let me know?

Happy Thanksgivings!

 


Playing with SUBSTR and INSTR

Thu, 2016-11-17 01:18
hawk:(SYS@hawk):PRIMARY> r
  1  SELECT handle,
  2  SUBSTR(handle, INSTR(handle,'/',-1)+1)                extract_last_field,
  3  SUBSTR(handle, 1, (INSTR(handle,'/',-1,1)-1))         remove_last_field,
  4  SUBSTR(handle, 1, (INSTR(handle,'/',1,2))-1)          extract_first_field,
  5  SUBSTR(handle, INSTR(handle,'/',1,2), length(handle)) remove_first_field
  6* FROM v$backup_piece_details

HANDLE
--------------------------------------------------------------------------------
EXTRACT_LAST_FIELD
--------------------------------------------------------------------------------
REMOVE_LAST_FIELD
--------------------------------------------------------------------------------
EXTRACT_FIRST_FIELD
--------------------------------------------------------------------------------
REMOVE_FIRST_FIELD
--------------------------------------------------------------------------------
/oradata/backup/HAWK_3183859104_20161116_24rl3690_1_1.bsu
HAWK_3183859104_20161116_24rl3690_1_1.bsu
/oradata/backup
/oradata
/backup/HAWK_3183859104_20161116_24rl3690_1_1.bsu

/oradata/backup/HAWK_3183859104_20161116_1vrl35ev_2_1.bkp
HAWK_3183859104_20161116_1vrl35ev_2_1.bkp
/oradata/backup
/oradata
/backup/HAWK_3183859104_20161116_1vrl35ev_2_1.bkp

hawk:(SYS@hawk):PRIMARY> r
  1  SELECT name,
  2  SUBSTR(name, INSTR(name,'/',-1)+1)              extract_last_field,
  3  SUBSTR(name, 1, (INSTR(name,'/',-1,1)-1))       remove_last_field,
  4  SUBSTR(name, 1, (INSTR(name,'/',1,2))-1)        extract_first_field,
  5  SUBSTR(name, INSTR(name,'/',1,2), length(name)) remove_first_field
  6* FROM v$datafile

NAME
--------------------------------------------------------------------------------
EXTRACT_LAST_FIELD
--------------------------------------------------------------------------------
REMOVE_LAST_FIELD
--------------------------------------------------------------------------------
EXTRACT_FIRST_FIELD
--------------------------------------------------------------------------------
REMOVE_FIRST_FIELD
--------------------------------------------------------------------------------
/oradata/SGN_HAWK/datafile/o1_mf_system_d00pchld_.dbf
o1_mf_system_d00pchld_.dbf
/oradata/SGN_HAWK/datafile
/oradata
/SGN_HAWK/datafile/o1_mf_system_d00pchld_.dbf

/oradata/SGN_HAWK/datafile/o1_mf_sysaux_d00pdw4l_.dbf
o1_mf_sysaux_d00pdw4l_.dbf
/oradata/SGN_HAWK/datafile
/oradata
/SGN_HAWK/datafile/o1_mf_sysaux_d00pdw4l_.dbf

hawk:(SYS@hawk):PRIMARY>

OGG Activity Logging Tracing (Doc ID 1204284.1)

Mon, 2016-11-14 19:54

I just came across MOS Doc for tracing OGG processes.

Just thought I would compare the old versus new.

You can find comparison and my preference here


srvctl start/stop/status home

Sun, 2016-11-06 20:40

Learning new things by actually reading the log files.

[oracle@rac02:emu2:/home/oracle]
$ srvctl status home -o /u01/app/oracle/product/11.2.0.4/db_1 -s ~/statushome.txt -n rac01; cat ~/statushome.txt
Service emu_svc is running on node rac01
Database emu is running on node rac01
svc-emu_svc
db-emu


[oracle@rac02:emu2:/home/oracle]
$ srvctl stop home -o /u01/app/oracle/product/11.2.0.4/db_1 -s ~/stophome.txt -n rac01 -t immediate -f
PRKO-3244 : The -s  option value "/home/oracle/stophome.txt" already exists


[oracle@rac02:emu2:/home/oracle]
$ rm -f ~/stophome.txt; srvctl stop home -o /u01/app/oracle/product/11.2.0.4/db_1 -s ~/stophome.txt -n rac01 -t immediate -f; cat ~/stophome.txt
svc-emu_svc
db-emu


[oracle@rac02:emu2:/home/oracle]
$ srvctl start home -o /u01/app/oracle/product/11.2.0.4/db_1 -s ~/stophome.txt -n rac01; cat ~/stophome.txt
svc-emu_svc
db-emu


[oracle@rac02:emu2:/home/oracle]
$ srvctl status home -o /u01/app/oracle/product/11.2.0.4/db_1 -s ~/statushome.txt -n rac01; cat ~/statushome.txt
Service emu_svc is running on node rac01
Database emu is running on node rac01
svc-emu_svc
db-emu

Using opatch report feature

Sat, 2016-11-05 13:51

I have been patching a lot lately and started to wonder if this is a simpler way to check for patch conflict.

Reviewing the log from running opatch auto shows opatch auto does perform check; hence, why do we need to check this manually?

What about creating scripts to do this? Done this as well and will required modifications when requirements are changed.

If the post name did not give it away or if you want more details, then take a look at opatch auto report

Here is the script I have to check for conflict if you are still interested.
#!/bin/sh -ex
export PATCH_TOP_DIR=/media/sf_Linux/11.2.0.4/Oct2106PSU
export GRID_HOME=`grep ':' /etc/oratab|grep -v '^#'|awk -F ":" '{print $2}'|grep 'grid'|uniq`
export ORACLE_HOME=`grep ':' /etc/oratab|grep -v '^#'|awk -F ":" '{print $2}'|grep 'db'|uniq`
export AGENT_HOME=
env|egrep 'PATCH|GRID_HOME|ORACLE_HOME|AGENT_HOME'
#
ls -lrt $PATCH_TOP_DIR
ls -ld $GRID_HOME
ls -ld $ORACLE_HOME
#
ps -ef|grep pmon
ps -ef|grep tns
ps -ef|grep agent_inst
#
grep ':' /etc/oratab|grep -v '^#'
grep ':' /etc/oratab|grep -v '^#'|awk -F ":" '{print $2}'|uniq
which make ar ld nm
#
echo "Check OPatch Version"
$GRID_HOME/OPatch/opatch version -oh $GRID_HOME -v2c 11.2.0.3.6
$ORACLE_HOME/OPatch/opatch version -oh $ORACLE_HOME -v2c 11.2.0.3.6
#
echo "Check Conflict for 24436338"
$GRID_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -oh $GRID_HOME -phBaseDir $PATCH_TOP_DIR/24436338
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -oh $ORACLE_HOME -phBaseDir $PATCH_TOP_DIR/24436338
#
echo "Check Conflict for 24315821"
$GRID_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -oh $GRID_HOME -phBaseDir $PATCH_TOP_DIR/24315821
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -oh $ORACLE_HOME -phBaseDir $PATCH_TOP_DIR/24315821
#
echo "Check lsinventory > /tmp/gi_inv.txt"
$GRID_HOME/OPatch/opatch lspatches -oh $GRID_HOME
$GRID_HOME/OPatch/opatch lsinventory -detail -oh $GRID_HOME > /tmp/gi_inv.txt
#
echo "Check lsinventory > /tmp/db_inv.txt"
$ORACLE_HOME/OPatch/opatch lspatches -oh $ORACLE_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME > /tmp/db_inv.txt
#
head -50 /tmp/gi_inv.txt
tail -50 /tmp/gi_inv.txt
head -50 /tmp/db_inv.txt
tail -50 /tmp/db_inv.txt
echo "******** COMPLETED ********"
exit
 Here are the results from running the script.
[oracle@rac01:emu1:/media/sf_Linux/scripts]
$ ./check_psu.sh
+ export PATCH_TOP_DIR=/media/sf_Linux/11.2.0.4/Oct2106PSU
+ PATCH_TOP_DIR=/media/sf_Linux/11.2.0.4/Oct2106PSU
++ grep : /etc/oratab
++ grep -v '^#'
++ awk -F : '{print $2}'
++ grep grid
++ uniq
+ export GRID_HOME=/u01/app/11.2.0.4/grid
+ GRID_HOME=/u01/app/11.2.0.4/grid
++ grep : /etc/oratab
++ grep db
++ awk -F : '{print $2}'
++ grep -v '^#'
++ uniq
+ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
+ ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
+ export AGENT_HOME=
+ AGENT_HOME=
+ env
+ egrep 'PATCH|GRID_HOME|ORACLE_HOME|AGENT_HOME'
AGENT_HOME=
GRID_HOME=/u01/app/11.2.0.4/grid
PATCH_TOP_DIR=/media/sf_Linux/11.2.0.4/Oct2106PSU
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
+ ls -lrt /media/sf_Linux/11.2.0.4/Oct2106PSU
total 736824
drwxrwx---. 1 root vboxsf      4096 Sep 14 04:40 24436338
drwxrwx---. 1 root vboxsf      4096 Sep 15 09:00 24315821
-rwxrwx---. 1 root vboxsf    112021 Oct 18 07:09 PatchSearch.xml
-rwxrwx---. 1 root vboxsf  42351279 Nov  5 11:25 p24315821_112040_Linux-x86-64.zip
-rwxrwx---. 1 root vboxsf 712035213 Nov  5 11:28 p24436338_112040_Linux-x86-64.zip
+ ls -ld /u01/app/11.2.0.4/grid
drwxr-xr-x. 72 root oinstall 4096 Nov  5 06:40 /u01/app/11.2.0.4/grid
+ ls -ld /u01/app/oracle/product/11.2.0.4/db_1
drwxrwxr-x. 79 oracle oinstall 4096 Nov  5 11:39 /u01/app/oracle/product/11.2.0.4/db_1
+ ps -ef
+ grep pmon
oracle    1969     1  0 10:12 ?        00:00:01 ora_pmon_emu1_1
grid      3654     1  0 06:42 ?        00:00:03 asm_pmon_+ASM1
oracle    9458  9439  0 11:47 pts/0    00:00:00 grep pmon
+ ps -ef
+ grep tns
root        19     2  0 Nov04 ?        00:00:00 [netns]
grid      4234     1  0 06:43 ?        00:00:00 /u01/app/11.2.0.4/grid/bin/tnslsnr LISTENER -inherit
grid      4255     1  0 06:43 ?        00:00:00 /u01/app/11.2.0.4/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle    9460  9439  0 11:47 pts/0    00:00:00 grep tns
+ ps -ef
+ grep agent_inst
oracle    9462  9439  0 11:47 pts/0    00:00:00 grep agent_inst
+ grep : /etc/oratab
+ grep -v '^#'
+ASM1:/u01/app/11.2.0.4/grid:N          # line added by Agent
db01:/u01/app/oracle/product/11.2.0.4/db_1:N
emu1:/u01/app/oracle/product/11.2.0.4/db_1:N
emu1_1:/u01/app/oracle/product/11.2.0.4/db_1:N
emu1_2:/u01/app/oracle/product/11.2.0.4/db_1:N
emu:/u01/app/oracle/product/11.2.0.4/db_1:N             # line added by Agent
+ grep : /etc/oratab
+ grep -v '^#'
+ uniq
+ awk -F : '{print $2}'
/u01/app/11.2.0.4/grid
/u01/app/oracle/product/11.2.0.4/db_1
+ which make ar ld nm
/usr/bin/make
/usr/bin/ar
/usr/bin/ld
/usr/bin/nm
+ echo 'Check OPatch Version'
Check OPatch Version
+ /u01/app/11.2.0.4/grid/OPatch/opatch version -oh /u01/app/11.2.0.4/grid -v2c 11.2.0.3.6
OPatch could not create/open history file for writing.

Oracle Interim Patch Installer version 11.2.0.3.15
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/11.2.0.4/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/11.2.0.4/grid/oraInst.loc
OPatch version    : 11.2.0.3.15
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11.2.0.4/grid/cfgtoollogs/opatch/opatch2016-11-05_11-47-31AM_1.log


OPatch version for all Oracle Homes registered in Central Inventory:
Location                   Version
/u01/app/11.2.0.4/grid     11.2.0.3.15


All of the Oracle Home(s) pass the Minimum OPatch Version requirement.


OPatch succeeded.
+ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch version -oh /u01/app/oracle/product/11.2.0.4/db_1 -v2c 11.2.0.3.6
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2016-11-05_11-47-32AM_1.log


OPatch version for all Oracle Homes registered in Central Inventory:
Location                                  Version
/u01/app/oracle/product/11.2.0.4/db_1     11.2.0.3.12


All of the Oracle Home(s) pass the Minimum OPatch Version requirement.


OPatch succeeded.
+ echo 'Check Conflict for 24436338'
Check Conflict for 24436338
+ /u01/app/11.2.0.4/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -oh /u01/app/11.2.0.4/grid -phBaseDir /media/sf_Linux/11.2.0.4/Oct2106PSU/24436338
OPatch could not create/open history file for writing.

Oracle Interim Patch Installer version 11.2.0.3.15
Copyright (c) 2016, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/11.2.0.4/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/11.2.0.4/grid/oraInst.loc
OPatch version    : 11.2.0.3.15
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11.2.0.4/grid/cfgtoollogs/opatch/opatch2016-11-05_11-47-33AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
+ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -oh /u01/app/oracle/product/11.2.0.4/db_1 -phBaseDir /media/sf_Linux/11.2.0.4/Oct2106PSU/24436338
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2016-11-05_11-47-39AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
+ echo 'Check Conflict for 24315821'
Check Conflict for 24315821
+ /u01/app/11.2.0.4/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -oh /u01/app/11.2.0.4/grid -phBaseDir /media/sf_Linux/11.2.0.4/Oct2106PSU/24315821
OPatch could not create/open history file for writing.

Oracle Interim Patch Installer version 11.2.0.3.15
Copyright (c) 2016, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/11.2.0.4/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/11.2.0.4/grid/oraInst.loc
OPatch version    : 11.2.0.3.15
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11.2.0.4/grid/cfgtoollogs/opatch/opatch2016-11-05_11-47-47AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
+ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -oh /u01/app/oracle/product/11.2.0.4/db_1 -phBaseDir /media/sf_Linux/11.2.0.4/Oct2106PSU/24315821
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2016-11-05_11-47-48AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
+ echo 'Check lsinventory > /tmp/gi_inv.txt'
Check lsinventory > /tmp/gi_inv.txt
+ /u01/app/11.2.0.4/grid/OPatch/opatch lspatches -oh /u01/app/11.2.0.4/grid
OPatch could not create/open history file for writing.

22502505;ACFS Patch Set Update : 11.2.0.4.160419 (22502505)
22502549;OCW Patch Set Update : 11.2.0.4.160419 (22502549)
22502456;Database Patch Set Update : 11.2.0.4.160419 (22502456)

OPatch succeeded.
+ /u01/app/11.2.0.4/grid/OPatch/opatch lsinventory -detail -oh /u01/app/11.2.0.4/grid
+ echo 'Check lsinventory > /tmp/db_inv.txt'
Check lsinventory > /tmp/db_inv.txt
+ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch lspatches -oh /u01/app/oracle/product/11.2.0.4/db_1
22502549;OCW Patch Set Update : 11.2.0.4.160419 (22502549)
22502456;Database Patch Set Update : 11.2.0.4.160419 (22502456)

OPatch succeeded.
+ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/11.2.0.4/db_1
+ head -50 /tmp/gi_inv.txt
OPatch could not create/open history file for writing.

Oracle Interim Patch Installer version 11.2.0.3.15
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/11.2.0.4/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/11.2.0.4/grid/oraInst.loc
OPatch version    : 11.2.0.3.15
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11.2.0.4/grid/cfgtoollogs/opatch/opatch2016-11-05_11-47-52AM_1.log

Lsinventory Output file location : /u01/app/11.2.0.4/grid/cfgtoollogs/opatch/lsinv/lsinventory2016-11-05_11-47-52AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac01
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Grid Infrastructure 11g                                       11.2.0.4.0
There are 1 products installed in this Oracle Home.


Installed Products (89):

Agent Required Support Files                                         10.2.0.4.5
Assistant Common Files                                               11.2.0.4.0
Automatic Storage Management Assistant                               11.2.0.4.0
Bali Share                                                           1.1.18.0.0
Buildtools Common Files                                              11.2.0.4.0
Character Set Migration Utility                                      11.2.0.4.0
Cluster Ready Services Files                                         11.2.0.4.0
Cluster Verification Utility Common Files                            11.2.0.4.0
Cluster Verification Utility Files                                   11.2.0.4.0
Database SQL Scripts                                                 11.2.0.4.0
Deinstallation Tool                                                  11.2.0.4.0
Enterprise Manager Common Core Files                                 10.2.0.4.5
Enterprise Manager Common Files                                      10.2.0.4.5
Enterprise Manager plugin Common Files                               11.2.0.4.0
Expat libraries                                                       2.0.1.0.1
HAS Common Files                                                     11.2.0.4.0
HAS Files for DB                                                     11.2.0.4.0
Installation Common Files                                            11.2.0.4.0
Installation Plugin Files                                            11.2.0.4.0
Installer SDK Component                                              11.2.0.4.0
Java Development Kit                                                1.5.0.51.10
+ tail -50 /tmp/gi_inv.txt
     oraus.msg --> ORACLE_HOME/rdbms/mesg/oraus.msg
     ordim.jar --> ORACLE_HOME/ord/jlib/ordim.jar
     ordimimg.jar --> ORACLE_HOME/ord/jlib/ordimimg.jar
     orion.o --> ORACLE_HOME/rdbms/lib/orion.o
     prvtadv.plb --> ORACLE_HOME/rdbms/admin/prvtadv.plb
     prvtblin.plb --> ORACLE_HOME/rdbms/admin/prvtblin.plb
     prvtbsdp.plb --> ORACLE_HOME/rdbms/admin/prvtbsdp.plb
     prvtbsmt.plb --> ORACLE_HOME/rdbms/admin/prvtbsmt.plb
     prvtbstr.plb --> ORACLE_HOME/rdbms/admin/prvtbstr.plb
     prvtdadv.plb --> ORACLE_HOME/rdbms/admin/prvtdadv.plb
     prvthsdp.plb --> ORACLE_HOME/rdbms/admin/prvthsdp.plb
     prvthsmt.plb --> ORACLE_HOME/rdbms/admin/prvthsmt.plb
     prvthstr.plb --> ORACLE_HOME/rdbms/admin/prvthstr.plb
     prvtlmcb.plb --> ORACLE_HOME/rdbms/admin/prvtlmcb.plb
     prvtlmd.plb --> ORACLE_HOME/rdbms/admin/prvtlmd.plb
     prvtpckl.plb --> ORACLE_HOME/rdbms/admin/prvtpckl.plb
     prvtredacta.plb --> ORACLE_HOME/rdbms/admin/prvtredacta.plb
     prvtutil.plb --> ORACLE_HOME/rdbms/admin/prvtutil.plb
     recover.bsq --> ORACLE_HOME/rdbms/admin/recover.bsq
     schema --> ORACLE_HOME/bin/schema
     sdodbmig.sql --> ORACLE_HOME/md/admin/sdodbmig.sql
     sdopatch.sql --> ORACLE_HOME/md/admin/sdopatch.sql
     sjsex.o --> ORACLE_HOME/rdbms/lib/sjsex.o
     spawrio.sql --> ORACLE_HOME/rdbms/admin/spawrio.sql
     utlu112i.sql --> ORACLE_HOME/rdbms/admin/utlu112i.sql
     utlu112x.sql --> ORACLE_HOME/rdbms/admin/utlu112x.sql
     xml --> ORACLE_HOME/bin/xml
   Patch Location in Inventory:
     /u01/app/11.2.0.4/grid/inventory/oneoffs/22502456
     /u01/app/11.2.0.4/grid/inventory/oneoffs/21948347
     /u01/app/11.2.0.4/grid/inventory/oneoffs/21352635
     /u01/app/11.2.0.4/grid/inventory/oneoffs/20760982
     /u01/app/11.2.0.4/grid/inventory/oneoffs/20299013
     /u01/app/11.2.0.4/grid/inventory/oneoffs/19769489
     /u01/app/11.2.0.4/grid/inventory/oneoffs/19121551
     /u01/app/11.2.0.4/grid/inventory/oneoffs/18522509
     /u01/app/11.2.0.4/grid/inventory/oneoffs/18031668
     /u01/app/11.2.0.4/grid/inventory/oneoffs/17478514
   Patch Location in Storage area:
     /u01/app/11.2.0.4/grid/.patch_storage/22502456_Mar_21_2016_11_49_22



Rac system comprising of multiple nodes
  Local node = rac01
  Remote node = rac02

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

OPatch succeeded.
+ head -50 /tmp/db_inv.txt
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2016-11-05_11-47-55AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/lsinv/lsinventory2016-11-05_11-47-55AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac01
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.


Installed Products (136):

Agent Required Support Files                                         10.2.0.4.5
Assistant Common Files                                               11.2.0.4.0
Bali Share                                                           1.1.18.0.0
Buildtools Common Files                                              11.2.0.4.0
Character Set Migration Utility                                      11.2.0.4.0
Cluster Verification Utility Common Files                            11.2.0.4.0
Database Configuration and Upgrade Assistants                        11.2.0.4.0
Database SQL Scripts                                                 11.2.0.4.0
Database Workspace Manager                                           11.2.0.4.0
Deinstallation Tool                                                  11.2.0.4.0
Enterprise Edition Options                                           11.2.0.4.0
Enterprise Manager Agent                                             10.2.0.4.5
Enterprise Manager Agent Core Files                                  10.2.0.4.5
Enterprise Manager Common Core Files                                 10.2.0.4.5
Enterprise Manager Common Files                                      10.2.0.4.5
Enterprise Manager Database Plugin -- Agent Support                  11.2.0.4.0
Enterprise Manager Database Plugin -- Repository Support             11.2.0.4.0
Enterprise Manager Grid Control Core Files                           10.2.0.4.5
Enterprise Manager plugin Common Files                               11.2.0.4.0
Enterprise Manager Repository Core Files                             10.2.0.4.5
Exadata Storage Server                                               11.2.0.1.0
Expat libraries                                                       2.0.1.0.1
Generic Connectivity Common Files                                    11.2.0.4.0
+ tail -50 /tmp/db_inv.txt
     prvtblin.plb --> ORACLE_HOME/rdbms/admin/prvtblin.plb
     prvtbsdp.plb --> ORACLE_HOME/rdbms/admin/prvtbsdp.plb
     prvtbsmt.plb --> ORACLE_HOME/rdbms/admin/prvtbsmt.plb
     prvtbstr.plb --> ORACLE_HOME/rdbms/admin/prvtbstr.plb
     prvtdadv.plb --> ORACLE_HOME/rdbms/admin/prvtdadv.plb
     prvthsdp.plb --> ORACLE_HOME/rdbms/admin/prvthsdp.plb
     prvthsmt.plb --> ORACLE_HOME/rdbms/admin/prvthsmt.plb
     prvthstr.plb --> ORACLE_HOME/rdbms/admin/prvthstr.plb
     prvtlmcb.plb --> ORACLE_HOME/rdbms/admin/prvtlmcb.plb
     prvtlmd.plb --> ORACLE_HOME/rdbms/admin/prvtlmd.plb
     prvtpckl.plb --> ORACLE_HOME/rdbms/admin/prvtpckl.plb
     prvtredacta.plb --> ORACLE_HOME/rdbms/admin/prvtredacta.plb
     prvtutil.plb --> ORACLE_HOME/rdbms/admin/prvtutil.plb
     recover.bsq --> ORACLE_HOME/rdbms/admin/recover.bsq
     schema --> ORACLE_HOME/bin/schema
     sdodbmig.sql --> ORACLE_HOME/md/admin/sdodbmig.sql
     sdopatch.sql --> ORACLE_HOME/md/admin/sdopatch.sql
     sjsex.o --> ORACLE_HOME/rdbms/lib/sjsex.o
     spawrio.sql --> ORACLE_HOME/rdbms/admin/spawrio.sql
     utlu112i.sql --> ORACLE_HOME/rdbms/admin/utlu112i.sql
     utlu112x.sql --> ORACLE_HOME/rdbms/admin/utlu112x.sql
     xml --> ORACLE_HOME/bin/xml
     xmlcg --> ORACLE_HOME/bin/xmlcg
     xmldiff --> ORACLE_HOME/bin/xmldiff
     xmlpatch --> ORACLE_HOME/bin/xmlpatch
     xsl --> ORACLE_HOME/bin/xsl
     xvm --> ORACLE_HOME/bin/xvm
   Patch Location in Inventory:
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/22502456
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/21948347
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/21352635
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/20760982
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/20299013
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/19769489
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/19121551
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/18522509
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/18031668
     /u01/app/oracle/product/11.2.0.4/db_1/inventory/oneoffs/17478514
   Patch Location in Storage area:
     /u01/app/oracle/product/11.2.0.4/db_1/.patch_storage/22502456_Mar_21_2016_11_49_22



Rac system comprising of multiple nodes
  Local node = rac01
  Remote node = rac02

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

OPatch succeeded.
+ echo '******** COMPLETED ********'
******** COMPLETED ********
+ exit
[oracle@rac01:emu1:/media/sf_Linux/scripts]
$

Instance Name changes during RAC One Node Failover/Switchover

Thu, 2016-11-03 07:07

Just a quick note to self.

INSTANCE / NODE CRASH (FAILOVER):

     When the node goes down due to abnormal termination (  kill -9 or due to unknown reasons) Grid Infrastructure performs the following:

  •  First cluster tries to restart the instance in same node.
  •  If it is unsuccessful then it will start the instance in other node.
  •  If the instance name is orcl_1, when it failover to node 2 it will be still orcl_1.

INSTANCE RELOCATE MANUALLY(SWITCHOVER):

    When Manually switchover is performed for relocating orcl_1 from Node1 to Node 2 (Instance Relocation) Grid Infrastructure performs the following:

  • It will first create pfile( init.ora) automatically in node 2.
  • In node 2 it will use the new pfile and start instance with a new name, i.e. orcl_2, this is due to both instances are required to be up and running for active sessions on the original node to complete or time out.
  • Post switchover, it uses new instance name i.e. orcl_2   

Reference:

(31-May-2016). Retrieved November 3, 2016, from https://support.oracle.com/epmos/faces/DocContentDisplay?id=1540629.1


Oracle 12c CRS-4995: The command ‘Modify resource’ is invalid in crsctl.

Wed, 2016-11-02 21:23

So there I was playing with 12c Oracle restart and ran into
CRS-4995: The command ‘Modify resource’ is invalid in crsctl. Use srvctl for this command.

For 12c, this expected as crsctl shouldn’t be used to modify ora.* resource.

Couldn’t figure out how to modify resource using svrctl.

However, I did learn about -unsupported for crsctl.

hawk:(SYS@hawk):PRIMARY> show parameter db%name

NAME                                 TYPE                           VALUE
------------------------------------ ------------------------------ ------------------------------
db_file_name_convert                 string
db_name                              string                         hawk
db_unique_name                       string                         sgn_hawk
pdb_file_name_convert                string
hawk:(SYS@hawk):PRIMARY>

oracle@arrow1:hawk:/home/oracle
$ echo $ORACLE_SID;echo $ORACLE_HOME
hawk
/u01/app/oracle/product/12.1.0.2/db_1

oracle@arrow1:hawk:/home/oracle
$ srvctl add database -db sgn_hawk -o $ORACLE_HOME -p $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora -startoption open -stopoption immediate -dbname hawk

++++++++++

oracle@arrow1:hawk:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0.2/db_1

oracle@arrow1:hawk:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ cat listener.ora
listener=(description=(address=(protocol=tcp)(host=)(port=1521)
LISTENER_12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1551))
      (ADDRESS=(PROTOCOL=tcp)(HOST=arrow1.localdomain)(PORT=1551)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767))
    )
  )

SID_LIST_LISTENER_12C =
  (SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = sgn_hawk_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = hawk)
    )
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = test2)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = test2)
    )
  )

USE_SID_AS_SERVICE_LISTENER_12C = ON
INBOUND_CONNECT_TIMEOUT_LISTENER_12C = 120
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_12C = OFF
DIAG_ADR_ENABLED_LISTENER_12C = OFF
LOGGING_LISTENER_12C = OFF
TRACE_LEVEL_LISTENER_12C = OFF
SAVE_CONFIG_ON_STOP_LISTENER_12C = TRUE

oracle@arrow1:hawk:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ srvctl add listener -l LISTENER_12C -p TCP:1551 -o $ORACLE_HOME
oracle@arrow1:hawk:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$

++++++++++
oracle@arrow1:grid:/home/oracle
$ srvctl config database
sgn_hawk

oracle@arrow1:grid:/home/oracle
$ srvctl config listener
Name: LISTENER_12C
Type: Database Listener
Home: /u01/app/oracle/product/12.1.0.2/db_1
End points: TCP:1551
Listener is enabled.

+++++++++

oracle@arrow1:grid:/home/oracle
$ crsctl stat res -t -w "TYPE = ora.listener.type"|grep lsnr
ora.LISTENER_12C.lsnr

oracle@arrow1:grid:/home/oracle
$ crsctl stat res -t -w "TYPE = ora.database.type"|grep db
ora.sgn_hawk.db

oracle@arrow1:grid:/home/oracle
$ crsctl stat res ora.sgn_hawk.db -p | grep AUTO_START
AUTO_START=restore

++++++++++

Why database not startup automatic 11gR2 Grid Infrastructure?
http://surachartopun.com/2011/04/why-database-not-startup-automatic.html

AUTO_START
- always: 
Restarts the resource when the server restarts regardless of the state of the resource when the server stopped.

- restore: 
Restores the resource to the same state that it was in when the server stopped. 
Oracle Clusterware attempts to restart the resource if the value of TARGET was ONLINE before the server stopped.

- never: 
Oracle Clusterware never restarts the resource regardless of the state of the resource when the server stopped.

++++++++++

oracle@arrow1:grid:/home/oracle
$ crsctl modify resource ora.sgn_hawk.db -attr AUTO_START=always
CRS-4995:  The command 'Modify  resource' is invalid in crsctl. Use srvctl for this command.

++++++++++

crsctl modify ora.* resource fails with CRS-4995 in 12.1.0.2 and above (Doc ID 1918102.1)
Alternative Procedure To Install and Configure ASM 12.1.0.2.0 On Unix/Linux Configurations (Standalone) (Doc ID 1954213.1)

oracle@arrow1:grid:/home/oracle
$ crsctl modify resource ora.sgn_hawk.db -attr AUTO_START=always -unsupported

oracle@arrow1:grid:/home/oracle
$ crsctl stat res ora.sgn_hawk.db -p | grep AUTO_START
AUTO_START=always

Identify SQL_ID from Failed Per-SQL Time Limit SQL_TUNING_TASK

Tue, 2016-11-01 21:32

First, many thanks to Ivica Arsov from Pythian for all his help is solving the issue.

The objective is to find the 2 SQLs failed due to time limit.

Advance apologies for lazy post as I am just going to only provide references and results from triage.

$ oerr ora 16957
16957, 00000, "SQL Analyze time limit interrupt"
// *Cause: This is an internal error code used indicate that SQL analyze has
//         reached its time limit.
// *Action:

$ oerr ora 13639
13639, 00000, "The current operation was interrupted because it timed out."
// *Cause:  The task or object operation timed out.
// *Action: None

+++++++++++

SQL> 
SELECT DBMS_AUTO_SQLTUNE.report_auto_tuning_task FROM dual;

REPORT_AUTO_TUNING_TASK
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Workload Type                           : Automatic High-Load SQL Workload
Execution Count                         : 39
Current Execution                       : EXEC_17554
Execution Type                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global Time Limit(seconds)              : 14400
Per-SQL Time Limit(seconds)             : 2700
Completion Status                       : INTERRUPTED
Started at                              : 10/20/2016 22:00:01
Completed at                            : 10/21/2016 02:00:03
Number of Candidate SQLs                : 248
Cumulative Elapsed Time of SQL (s)      : 4837418

-------------------------------------------------------------------------------
Error: ORA-13639: The current operation was interrupted because it timed out.
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
                      Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed                      : 130
Number of SQLs in the Report                 : 23
Number of SQLs with Findings                 : 22
Number of SQLs with Statistic Findings       : 2
Number of SQLs with Alternative Plan Findings: 5
Number of SQLs with SQL profiles recommended : 14
Number of SQLs with Index Findings           : 11
Number of SQLs with SQL Restructure Findings : 3
Number of SQLs with Timeouts                 : 2
Number of SQLs with Errors                   : 1


Here are the references and what have been attempted.
It looks like attr7 varies by versions.

+++++++++

Automatic SQL Tune Job Fails With ORA-13639 (Doc ID 1363111.1)
Increase the job "Time Limit" Parameter to appropriate higher value to allow the tuning task to complete , using the following command :

Global Time Limit(seconds)              : 14400
Per-SQL Time Limit(seconds)             : 2700
Number of SQLs with Timeouts            : 2

+++++++

https://anargodjaev.wordpress.com/2014/07/22/ora-16957-sql-analyze-time-limit-interrupt-2/

SQL> r
  1  SELECT sql_id, sql_text FROM dba_hist_sqltext
  2  WHERE sql_id IN (SELECT attr1 FROM dba_advisor_objects
  3  WHERE execution_name = 'EXEC_17554'
  4  AND task_name = 'SYS_AUTO_SQL_TUNING_TASK'
  5  AND type = 'SQL' AND bitand(attr7,64) <> 0 )
  6*

no rows selected

SQL>

++++++++++

Bug 9874145 : PROCESS 0X0X00000002E2392938 APPEARS TO BE HUNG IN AUTO SQL TUNING TASK   
Product Version    11.1.0.7

SQL> select attr1 SQL_ID from dba_advisor_objects
  where task_name ='SYS_AUTO_SQL_TUNING_TASK'
  and type = 'SQL'
  and attr7 = 32
  order by 1;
 
SQL> r
  1* select attr7, count(*) from DBA_ADVISOR_OBJECTS where execution_name='EXEC_17554' and task_name ='SYS_AUTO_SQL_TUNING_TASK' and type='SQL' group by attr7

     ATTR7   COUNT(*)
---------- ----------
    32       99
     0       31

SQL> r
  1  SELECT count(sql_id)  FROM dba_hist_sqltext
  2  WHERE sql_id IN (SELECT attr1 FROM dba_advisor_objects
  3  WHERE execution_name = 'EXEC_17554'
  4  AND task_name = 'SYS_AUTO_SQL_TUNING_TASK'
  5  AND type = 'SQL' AND bitand(attr7,32) <> 0 )
  6*

COUNT(SQL_ID)
-------------
       99

SQL> r
  1  select  distinct sql_id
  2  from    DBA_ADVISOR_SQLPLANS
  3  where   execution_name = 'EXEC_17554'
  4         and sql_id not in (select sql_id from dba_advisor_sqlstats where execution_name = 'EXEC_17554')
  5*

SQL_ID
-------------
2dhfrqwrv0m16
58h2g858zqckc
apzyk43bfp6np
g84pgjy5ycw2g
20ypq7mzad6ah
cr1s7zpp7285p
ag01z6qn8450d
4z3gq9xh9a5jr
byav70kx3bj8w
8m3qru4z4687w
d28dgmyr5910n
5mgq2hd4xz6vv

12 rows selected.

SQL>

++++++++++

SQL> select sql_id from DBA_ADVISOR_SQLPLANS where EXECUTION_NAME='EXEC_17554'
minus
select sql_id from DBA_ADVISOR_SQLSTATS where EXECUTION_NAME='EXEC_17554'
;  2    3    4

SQL_ID
-------------
20ypq7mzad6ah
2dhfrqwrv0m16
4z3gq9xh9a5jr
58h2g858zqckc
5mgq2hd4xz6vv
8m3qru4z4687w
ag01z6qn8450d
apzyk43bfp6np
byav70kx3bj8w
cr1s7zpp7285p
d28dgmyr5910n
g84pgjy5ycw2g

12 rows selected.

SQL> select sql_id from DBA_ADVISOR_SQLSTATS where EXECUTION_NAME='EXEC_17554'
minus
select sql_id from DBA_ADVISOR_SQLPLANS where EXECUTION_NAME='EXEC_17554'
;  2    3    4

no rows selected

SQL>

++++++++++

case when finding_type = 3 AND bitand(finding_flags, 2) <> 0 then 1 else 0 end

1 - has timeout
0 - no timeout

finding_type and finding_flags are from sys.wri$_adv_findings table.

SQL> r
  1* select distinct TYPE, FLAGS from sys.wri$_adv_findings

      TYPE    FLAGS
---------- ----------
     4        1
     1        0
     4
     4        4
     2        0
     5        0
     2
     3        0
     4        0
     3        2
     4        9
     1
     3       16

13 rows selected.

SQL> r
  1  SELECT oe.*,
  2          f.id finding_id,
  3          f.type finding_type,
  4          f.flags finding_flags
  5  FROM
  6    (SELECT
  7       o.exec_name ,
  8        o.id object_id,
  9        o.attr1 sql_id,
 10        o.attr3 parsing_schema,
 11        to_number(NVL(o.attr5, '0')) phv,
 12        NVL(o.attr8,0) obj_attr8
 13      FROM  sys.wri$_adv_objects o
 14      WHERE o.exec_name = 'EXEC_17554'
 15      AND o.type      = 7
 16    ) oe,
 17    wri$_adv_findings f
 18  WHERE  f.exec_name (+)  = oe.exec_name
 19  AND f.obj_id (+)     = oe.object_id
 20  AND type             = 3
 21  AND bitand(flags,2) <> 0
 22*

EXEC_NAME        OBJECT_ID SQL_ID          PARSING_SCHEMA         PHV  OBJ_ATTR8 FINDING_ID FINDING_TYPE FINDING_FLAGS
--------------- ---------- --------------- --------------- ---------- ---------- ---------- ------------ -------------
EXEC_17554           29975 bxurwhv4muqj5   DEMO1234        2724156332 2438782765      31681            3             2
EXEC_17554           30015 1gzgzfuxn18v1   DEMO345670      4228937525 3182936792      31724            3             2

SQL> 

GoldenGate 12.2 EXCLUDEUSER

Sun, 2016-10-30 16:02

Here’s a good reason to always use the latest version.

GoldenGate 12c Integrated EXTRACT: How To EXCLUDEUSER (Doc ID 2008420.1)

APPLIES TO:

Oracle GoldenGate – Version 12.1.2.0.2 to 12.1.2.1.9 [Release 12.1]
Information in this document applies to any platform.

GOAL

We are trying to exclude the REPLICAT user in OGG 12.1.2 capturing from Oracle 12.1.0.2 bi-directional. But using EXTRACT TRANLOGOPTIONS EXCLUDEUSER to exclude a user in one of the PDBs:

TRANLOGOPTIONS EXCLUDEUSER ZEPP01.ZE_GG

EXTRACT is returning the message:

2015-03-25 15:44:27 ERROR OGG-00303 Could not find USER_ID corresponding to USERNAME ‘ZEPP01.ZE_GG’.

SOLUTION

When using Integrated EXTRACT, EXCLUDEUSER is currently not supported in OGG V12.1.2. We have a enhancement request tracked via Bug 21891811 – Support EXCLUDEUSER in Integrated Extract (IE) for DDL records which has been implementd in OGG v12.2.0.1.

For excluding users in a 12c multitenant DB, OGG v12.1.2  has a new EXTRACT param TRANLOGOPTIONS EXCLUDETAG <nn>. This is typically used to exclude the REPLICAT user in bi-directional configurations.

Changes made by Integrated REP are tagged by default in redo as 00. So adding the EXTRACT param TRANLOGOPTIONS EXCLUDETAG 00

Would exclude those operations.

The tag can also be explicitly set in REPLICAT using:

DBOPTIONS SETTAG 0935

Then in EXTRACT param:

TRANLOGOPTIONS EXCLUDETAG 0935

With OGG v12.2.0.1+, you can use the earlier option of TRANLOGOPTIONS EXCLUDEUSER ZEPP01.ZE_GG

Reference:

(2016, October 18). Retrieved October 30, 2016, from https://support.oracle.com/epmos/faces/DocContentDisplay?id=2008420.1


Working With Interval Datatype

Sat, 2016-10-29 10:41

Here is the scope:

The job takes 42m to complete and monitoring threshold is 30m.

If 42m is the typical to complete, then we should increase the threshold to avoid noise.

If 42m is not the typical time to complete, then we should investigate to see if this is an anomaly.

From dba_scheduler_job_run_details, RUN_DURATION  data type is INTERVAL DAY(3) TO SECOND(0).

Querying dba_scheduler_job_run_details provides the following result.

This seems to be a lot of work trying to identify jobs running longer that 30m, plus there can be errors as a run could have been missed which I did not highlight.

This test case was for 10.2.0.4 but imagine it would for all versions higher.

-- dba_scheduler_jobs
Enter value for 1: PURGE_JOB

OWNER			        JOB_NAME 		      STATE	      REPEAT_INTERVAL						   NEXT_RUN_DATE
------------------------------ ------------------------------ --------------- ------------------------------------------------------------ --------------------------------------------------
DEMODB01		       PURGE_JOB	      SCHEDULED       Freq=Hourly						   28-OCT-16 02.00.00.000000 PM -05:00

-- dba_scheduler_job_run_details

LOG_DATE			    JOB_NAME			   STATUS     ACTUAL_START_DATE 				 RUN_DURATION
----------------------------------- ------------------------------ ---------- -------------------------------------------------- ---------------
28-OCT-16 01.18.34.766342 PM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 01.00.00.101332 PM -05:00		 +000 00:18:35
28-OCT-16 12.18.50.630375 PM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 12.00.00.128330 PM -05:00		 +000 00:18:50
28-OCT-16 11.19.39.302422 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 11.00.00.096290 AM -05:00		 +000 00:19:39
28-OCT-16 10.19.06.631086 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 10.00.00.098943 AM -05:00		 +000 00:19:07
28-OCT-16 09.21.55.042321 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 09.00.00.142871 AM -05:00		 +000 00:21:55
28-OCT-16 08.22.05.168894 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 08.00.00.114841 AM -05:00		 +000 00:22:05
28-OCT-16 07.20.58.795685 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 07.00.00.615018 AM -05:00		 +000 00:20:58
28-OCT-16 06.19.32.286364 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 06.00.04.355748 AM -05:00		 +000 00:19:28
28-OCT-16 05.22.12.990840 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 05.00.00.108447 AM -05:00		 +000 00:22:13
28-OCT-16 04.22.46.316064 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 04.00.00.822142 AM -05:00		 +000 00:22:45
28-OCT-16 03.23.49.518493 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 03.00.00.767310 AM -05:00		 +000 00:23:49
28-OCT-16 02.33.20.747873 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 02.00.00.689275 AM -05:00		 +000 00:33:20
28-OCT-16 01.42.40.477522 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 01.00.00.687059 AM -05:00		 +000 00:42:40
28-OCT-16 12.24.03.200350 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 12.00.00.687291 AM -05:00		 +000 00:24:03
27-OCT-16 11.21.45.310127 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 11.00.00.174274 PM -05:00		 +000 00:21:45
27-OCT-16 10.22.04.226755 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 10.00.00.100101 PM -05:00		 +000 00:22:04
27-OCT-16 09.20.46.491481 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 09.00.00.095188 PM -05:00		 +000 00:20:46
27-OCT-16 08.20.11.936472 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 08.00.01.162137 PM -05:00		 +000 00:20:11
27-OCT-16 07.16.30.836698 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 07.00.00.122310 PM -05:00		 +000 00:16:31
27-OCT-16 06.17.19.315011 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 06.00.00.683161 PM -05:00		 +000 00:17:19
27-OCT-16 05.17.40.010428 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 05.00.00.093667 PM -05:00		 +000 00:17:40
27-OCT-16 04.18.08.064115 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 04.00.00.227937 PM -05:00		 +000 00:18:08
27-OCT-16 03.19.58.561961 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 03.00.00.136952 PM -05:00		 +000 00:19:58
27-OCT-16 02.17.30.492761 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 02.00.00.149822 PM -05:00		 +000 00:17:30
27-OCT-16 01.17.44.490772 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 01.00.00.084995 PM -05:00		 +000 00:17:44
27-OCT-16 12.19.29.486819 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 12.00.00.665087 PM -05:00		 +000 00:19:29
27-OCT-16 11.19.43.126793 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 11.00.00.095392 AM -05:00		 +000 00:19:43
27-OCT-16 10.20.28.096927 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 10.00.00.098227 AM -05:00		 +000 00:20:28
27-OCT-16 09.20.59.774236 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 09.00.00.087305 AM -05:00		 +000 00:21:00
27-OCT-16 08.20.11.194525 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 08.00.00.095362 AM -05:00		 +000 00:20:11
27-OCT-16 07.21.19.440710 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 07.00.00.093116 AM -05:00		 +000 00:21:19
27-OCT-16 06.21.21.777725 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 06.00.00.729176 AM -05:00		 +000 00:21:21
27-OCT-16 05.22.00.235810 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 05.00.00.093358 AM -05:00		 +000 00:22:00
27-OCT-16 04.22.41.035983 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 04.00.00.109282 AM -05:00		 +000 00:22:41
27-OCT-16 03.23.03.747790 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 03.00.00.740992 AM -05:00		 +000 00:23:03
27-OCT-16 02.28.28.902795 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 02.00.00.098180 AM -05:00		 +000 00:28:29
27-OCT-16 01.38.20.999365 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 01.00.00.618087 AM -05:00		 +000 00:38:20
27-OCT-16 12.22.48.596935 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 12.00.00.134944 AM -05:00		 +000 00:22:48
26-OCT-16 11.22.39.417841 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 11.00.00.601951 PM -05:00		 +000 00:22:39
26-OCT-16 10.21.24.088692 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 10.00.00.700209 PM -05:00		 +000 00:21:23
26-OCT-16 09.21.48.083949 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 09.00.00.108737 PM -05:00		 +000 00:21:48
26-OCT-16 08.20.36.600056 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 08.00.00.599238 PM -05:00		 +000 00:20:36
26-OCT-16 07.21.26.973000 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 07.00.00.170795 PM -05:00		 +000 00:21:27
26-OCT-16 06.21.13.513245 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 06.00.00.661959 PM -05:00		 +000 00:21:13
26-OCT-16 05.20.38.611174 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 05.00.00.095111 PM -05:00		 +000 00:20:39
26-OCT-16 04.19.43.026097 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 04.00.00.647115 PM -05:00		 +000 00:19:42
26-OCT-16 03.22.25.930388 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 03.00.00.099210 PM -05:00		 +000 00:22:26
26-OCT-16 02.22.28.929046 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 02.00.00.084756 PM -05:00		 +000 00:22:29
26-OCT-16 01.22.35.673399 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 01.00.00.092354 PM -05:00		 +000 00:22:36
26-OCT-16 12.22.51.518382 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 12.00.00.098155 PM -05:00		 +000 00:22:51
26-OCT-16 11.25.12.885694 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 11.00.00.097514 AM -05:00		 +000 00:25:13
26-OCT-16 10.25.30.697623 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 10.00.00.109984 AM -05:00		 +000 00:25:31
26-OCT-16 09.25.48.331970 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 09.00.00.650305 AM -05:00		 +000 00:25:48
26-OCT-16 08.23.00.786181 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 08.00.00.092182 AM -05:00		 +000 00:23:01
26-OCT-16 07.23.09.292134 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 07.00.00.706906 AM -05:00		 +000 00:23:09
26-OCT-16 06.23.26.838110 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 06.00.00.737269 AM -05:00		 +000 00:23:26
26-OCT-16 05.23.46.628555 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 05.00.00.779244 AM -05:00		 +000 00:23:46
26-OCT-16 04.26.10.248261 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 04.00.00.118811 AM -05:00		 +000 00:26:10
26-OCT-16 03.26.45.574405 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 03.00.00.868199 AM -05:00		 +000 00:26:45
26-OCT-16 02.29.59.220470 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 02.00.00.716143 AM -05:00		 +000 00:29:59
26-OCT-16 01.41.19.190372 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 01.00.00.671400 AM -05:00		 +000 00:41:19
26-OCT-16 12.40.14.668943 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 12.00.00.686242 AM -05:00		 +000 00:40:14
25-OCT-16 11.25.01.222486 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 11.00.00.094405 PM -05:00		 +000 00:25:01
25-OCT-16 10.25.16.188272 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 10.00.00.093149 PM -05:00		 +000 00:25:16
25-OCT-16 09.22.50.957227 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 09.00.00.685572 PM -05:00		 +000 00:22:50
25-OCT-16 08.23.24.366195 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 08.00.00.681146 PM -05:00		 +000 00:23:24
25-OCT-16 07.21.34.877025 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 07.00.00.679188 PM -05:00		 +000 00:21:34
25-OCT-16 06.22.01.687270 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 06.00.00.705931 PM -05:00		 +000 00:22:01
25-OCT-16 05.22.59.985410 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 05.00.00.058401 PM -05:00		 +000 00:23:00
25-OCT-16 04.23.47.510414 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 04.00.00.117113 PM -05:00		 +000 00:23:47
25-OCT-16 03.23.04.877802 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 03.00.00.099222 PM -05:00		 +000 00:23:05
25-OCT-16 02.26.03.301910 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 02.00.00.116152 PM -05:00		 +000 00:26:03
25-OCT-16 01.24.27.430723 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 01.00.00.117626 PM -05:00		 +000 00:24:27
25-OCT-16 12.24.36.971637 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 12.00.00.664118 PM -05:00		 +000 00:24:36
25-OCT-16 11.23.58.307402 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 11.00.00.096260 AM -05:00		 +000 00:23:58
25-OCT-16 10.24.31.716126 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 10.00.00.092256 AM -05:00		 +000 00:24:32
25-OCT-16 09.24.10.974503 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 09.00.00.100348 AM -05:00		 +000 00:24:11
25-OCT-16 08.22.53.833215 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 08.00.00.082274 AM -05:00		 +000 00:22:54
25-OCT-16 07.24.30.938431 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 07.00.00.692159 AM -05:00		 +000 00:24:30
25-OCT-16 06.23.52.431531 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 06.00.00.093246 AM -05:00		 +000 00:23:52
25-OCT-16 05.24.41.532133 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 05.00.00.729381 AM -05:00		 +000 00:24:41
25-OCT-16 04.23.25.958305 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 04.00.00.208211 AM -05:00		 +000 00:23:26
25-OCT-16 03.25.01.192060 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 03.00.00.106130 AM -05:00		 +000 00:25:01
25-OCT-16 02.28.53.376223 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 02.00.00.662053 AM -05:00		 +000 00:28:53
25-OCT-16 01.40.10.906657 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 01.00.00.690348 AM -05:00		 +000 00:40:10
25-OCT-16 12.26.06.567009 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 12.00.00.101229 AM -05:00		 +000 00:26:06
24-OCT-16 11.23.44.968956 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 11.00.00.112954 PM -05:00		 +000 00:23:45
24-OCT-16 10.25.27.790486 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 10.00.02.818356 PM -05:00		 +000 00:25:25
24-OCT-16 09.21.43.329295 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 09.00.00.143735 PM -05:00		 +000 00:21:43
24-OCT-16 08.23.53.456424 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 08.00.00.699357 PM -05:00		 +000 00:23:53
24-OCT-16 07.22.46.201840 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 07.00.00.108008 PM -05:00		 +000 00:22:46
24-OCT-16 06.24.31.090402 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 06.00.00.712135 PM -05:00		 +000 00:24:30
24-OCT-16 05.22.41.748800 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 05.00.00.092252 PM -05:00		 +000 00:22:42
24-OCT-16 04.24.25.687414 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 04.00.00.093241 PM -05:00		 +000 00:24:26
24-OCT-16 03.24.51.693561 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 03.00.00.734264 PM -05:00		 +000 00:24:51
24-OCT-16 02.23.25.886242 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 02.00.00.689991 PM -05:00		 +000 00:23:25
24-OCT-16 01.26.34.238241 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 01.00.00.097179 PM -05:00		 +000 00:26:34
24-OCT-16 12.26.56.946893 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 12.00.00.097951 PM -05:00		 +000 00:26:57

98 rows selected.

SQL >

Here’s the better way for  doing this using extract function.

Datatype is data type is INTERVAL DAY(3) TO SECOND(0)..

24 hours per day and 60 minutes per hour.

SQL > r
  1  SELECT log_date, job_name, extract(day from 24*60*RUN_DURATION) mins
  2  FROM dba_scheduler_job_run_details
  3  WHERE extract(day from 24*60*RUN_DURATION)>30
  4  AND job_name='PURGE_JOB'
  5  and log_date>sysdate-90
  6  ORDER BY 1 DESC
  7*

LOG_DATE			    JOB_NAME				 MINS
----------------------------------- ------------------------------ ----------
28-OCT-16 02.33.20.747873 AM -05:00 PURGE_JOB			   33
28-OCT-16 01.42.40.477522 AM -05:00 PURGE_JOB			   42
27-OCT-16 01.38.20.999365 AM -05:00 PURGE_JOB			   38
26-OCT-16 01.41.19.190372 AM -05:00 PURGE_JOB			   41
26-OCT-16 12.40.14.668943 AM -05:00 PURGE_JOB			   40
25-OCT-16 01.40.10.906657 AM -05:00 PURGE_JOB			   40
24-OCT-16 03.31.51.372573 AM -05:00 PURGE_JOB			   31
24-OCT-16 02.31.55.268260 AM -05:00 PURGE_JOB			   31
24-OCT-16 01.45.33.525611 AM -05:00 PURGE_JOB			   45
22-OCT-16 05.39.23.980162 AM -05:00 PURGE_JOB			   39
22-OCT-16 04.31.25.202116 AM -05:00 PURGE_JOB			   31
21-OCT-16 01.38.29.977490 AM -05:00 PURGE_JOB			   38
20-OCT-16 01.40.58.167010 AM -05:00 PURGE_JOB			   40
19-OCT-16 01.32.25.295604 AM -05:00 PURGE_JOB			   32
18-OCT-16 01.32.53.733495 AM -05:00 PURGE_JOB			   32
17-OCT-16 01.31.49.296251 AM -05:00 PURGE_JOB			   31
14-OCT-16 03.31.43.274818 AM -05:00 PURGE_JOB			   31
14-OCT-16 02.36.02.084047 AM -05:00 PURGE_JOB			   36
14-OCT-16 01.40.30.270864 AM -05:00 PURGE_JOB			   40
13-OCT-16 01.40.32.792297 AM -05:00 PURGE_JOB			   40
12-OCT-16 03.31.59.942443 AM -05:00 PURGE_JOB			   31
12-OCT-16 02.35.10.422916 AM -05:00 PURGE_JOB			   35
12-OCT-16 01.44.37.543112 AM -05:00 PURGE_JOB			   44
11-OCT-16 01.37.06.018139 AM -05:00 PURGE_JOB			   37
10-OCT-16 03.31.07.278843 AM -05:00 PURGE_JOB			   31
10-OCT-16 02.33.08.974312 AM -05:00 PURGE_JOB			   33
10-OCT-16 01.44.18.952183 AM -05:00 PURGE_JOB			   44
08-OCT-16 05.34.36.615095 AM -05:00 PURGE_JOB			   34
03-OCT-16 02.34.54.308012 AM -05:00 PURGE_JOB			   34
03-OCT-16 01.38.21.388805 AM -05:00 PURGE_JOB			   38
02-OCT-16 04.33.57.111031 PM -05:00 PURGE_JOB			   33
02-OCT-16 03.31.53.305048 AM -05:00 PURGE_JOB			   31
02-OCT-16 01.38.53.896792 AM -05:00 PURGE_JOB			   38
30-SEP-16 04.31.26.036246 AM -05:00 PURGE_JOB			   31
30-SEP-16 01.42.47.725811 AM -05:00 PURGE_JOB			   42
29-SEP-16 01.35.00.663317 AM -05:00 PURGE_JOB			   35

36 rows selected.

SQL > 

Quarterly CPU – To Patch or Not

Wed, 2016-10-26 08:13

If the organization is mandated by regulation, e.g. PCI, then it’s a no brainer.

What if the is no regulation and system is stable?

At this point, it depends on risk tolerance.

Review Oracle Critical Patch Update Advisory – October 2016 and search for Oracle Database Server Risk Matrix.

This example is for Oracle Database Server and please search accordingly for the required components.

Remote Exploit without Auth.? NO for all except Application Express

How much time is spent assessing risk and is it more efficient to spend the time patching?

It all boils down to time and tolerance.


Isolate Your Code

Tue, 2016-10-25 19:19

I fail to understand anonymous PL/SQL block is used with dbms_scheduler.

Here is an example:
hawk:(SYSTEM@hawk):PRIMARY> @x.sql
hawk:(SYSTEM@hawk):PRIMARY> set echo on
hawk:(SYSTEM@hawk):PRIMARY> BEGIN
  2  DBMS_SCHEDULER.CREATE_PROGRAM(
  3  program_name=>'TESTING',
  4  program_action=>'DECLARE
  5  x NUMBER := 100;
  6  BEGIN
  7     FOR i IN 1..10 LOOP
  8        IF MOD(i,2) = 0 THEN
  9           INSERT INTO temp VALUES (i);
 10        ELSE
 11           INSERT INTO temp VALUES (i);
 12        END IF;
 13        x := x + 100;
 14     END LOOP;
 15     COMMIT;
 16  END;',
 17  program_type=>'PLSQL_BLOCK',
 18  number_of_arguments=>0
 19  );
 20  END;
 21  /

PL/SQL procedure successfully completed.

hawk:(SYSTEM@hawk):PRIMARY> show error
No errors.
hawk:(SYSTEM@hawk):PRIMARY> -- exec DBMS_SCHEDULER.DROP_PROGRAM('TESTING');
Nothing wrong, right? What happens when we strip out and run the anonymous PL/SQL block?
hawk:(SYSTEM@hawk):PRIMARY> @y.sql
hawk:(SYSTEM@hawk):PRIMARY> DECLARE
  2     x NUMBER := 100;
  3  BEGIN
  4     FOR i IN 1..10 LOOP
  5        IF MOD(i,2) = 0 THEN
  6           INSERT INTO temp VALUES (i);
  7        ELSE
  8           INSERT INTO temp VALUES (i);
  9        END IF;
 10        x := x + 100;
 11     END LOOP;
 12     COMMIT;
 13  END;
 14  /
         INSERT INTO temp VALUES (i);
                     *
ERROR at line 6:
ORA-06550: line 6, column 22:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 6, column 10:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 22:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 8, column 10:
PL/SQL: SQL Statement ignored


hawk:(SYSTEM@hawk):PRIMARY> desc temp;
ERROR:
ORA-04043: object temp does not exist


hawk:(SYSTEM@hawk):PRIMARY>
Why not create stored procedure or package?
hawk:(SYSTEM@hawk):PRIMARY> @z.sql
hawk:(SYSTEM@hawk):PRIMARY> create or replace procedure SP_TESTING
  2  AS
  3  x NUMBER := 100;
  4  BEGIN
  5     FOR i IN 1..10 LOOP
  6        IF MOD(i,2) = 0 THEN
  7           INSERT INTO temp VALUES (i);
  8        ELSE
  9           INSERT INTO temp VALUES (i);
 10        END IF;
 11        x := x + 100;
 12     END LOOP;
 13     COMMIT;
 14  END;
 15  /

Warning: Procedure created with compilation errors.

hawk:(SYSTEM@hawk):PRIMARY> show error
Errors for PROCEDURE SP_TESTING:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/10     PL/SQL: SQL Statement ignored
7/22     PL/SQL: ORA-00942: table or view does not exist
9/10     PL/SQL: SQL Statement ignored
9/22     PL/SQL: ORA-00942: table or view does not exist

hawk:(SYSTEM@hawk):PRIMARY> create table temp(id int);

Table created.

hawk:(SYSTEM@hawk):PRIMARY> alter procedure SP_TESTING compile;

Procedure altered.

hawk:(SYSTEM@hawk):PRIMARY> show error
No errors.
hawk:(SYSTEM@hawk):PRIMARY> @a.sql
hawk:(SYSTEM@hawk):PRIMARY> BEGIN
  2  DBMS_SCHEDULER.CREATE_PROGRAM(
  3  program_name=>'TESTING2',
  4  program_action=>'BEGIN SP_TESTING; END;',
  5  program_type=>'PLSQL_BLOCK',
  6  number_of_arguments=>0
  7  );
  8  END;
  9  /

PL/SQL procedure successfully completed.

hawk:(SYSTEM@hawk):PRIMARY> show error
No errors.
hawk:(SYSTEM@hawk):PRIMARY> BEGIN SP_TESTING; END;
  2  /

PL/SQL procedure successfully completed.

hawk:(SYSTEM@hawk):PRIMARY> select * from temp;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

hawk:(SYSTEM@hawk):PRIMARY>

Pages