adrci> set homepath diag\rdbms\db11\db11
adrci> show incident

ADR Home = c:\app\ora11g\diag\rdbms\db11\db11:
********************************************************
INCIDENT_ID          PROBLEM_KEY
-------------------- -----------------------------------
8545                 ORA 600 [kcidr_io_check_common_6]


Note that the database did not and does not have to be opened or have an instance running, for that matter, in order to use ADRCI.
 
Use the "show home" command to see which ADR homes are available, followed by the "set homepath" as necessary.  Note that more than one home can be involved in an incident. Only the rdbms home need apply in this example for viewing a specific file.

adrci> set homepath diag\rdbms\db11\db11
adrci> show home
ADR Homes:
diag\rdbms\db11\db11

Being able to start the utility is obviously a good sign. Before starting, however, the platform or environment setup needs to be considered. There are a lot of things for free in a Windows environment that are not in a *NIX (all UNIX variants) one. In either case, check the diagnostic_dest  initialization parameter value in a SQL*Plus session. On the author's PC used throughout the remainder of this chapter for examples, diagnostic_dest is set to C:\APP\ORA11G.

SQL> show parameter diag

NAME                                 TYPE        VALUE

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

diagnostic_dest                      string      C:\APP\ORA11G

The important information to take away here is, of course, to ensure the DBA is working in the correct environment and with the database, or instance(s), of interest. RAC nodes and ASM will have their own ADR setup, and those could be spanning a common file system.

ADR deals with problems, so create one in order to have something to work with. Is every problem reportable, that is, is any ORA error recorded in the alert log sufficient to create an incident? The answer is no. Some errors are obviously serious and others represent PEBKAC (problem exists between keyboard and chair, i.e., the user). ADRCI will report on critical problems, which are the ones that Oracle considers to be critical which may not coincide with what the DBA believes is critical.

An easy error to generate and know that it will be recorded in the alert log is to attempt to shrink a datafile to a size lower than the high-watermark. Pick a datafile at random and attempt to resize it to something obviously nonsensical.

SQL> select file_name from dba_data_files;

FILE_NAME

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

C:\APP\ORA11G\ORADATA\DB11\USERS01.DBF

C:\APP\ORA11G\ORADATA\DB11\UNDOTBS01.DBF

C:\APP\ORA11G\ORADATA\DB11\SYSAUX01.DBF

C:\APP\ORA11G\ORADATA\DB11\SYSTEM01.DBF

C:\APP\ORA11G\ORADATA\DB11\EXAMPLE01.DBF

SQL> alter database datafile 'C:\APP\ORA11G\ORADATA\DB11\SYSTEM01.DBF'

  2  resize 2M;

alter database datafile 'C:\APP\ORA11G\ORADATA\DB11\SYSTEM01.DBF'

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

The tail of the (normal) alert log has recorded the error.

alter database datafile 'C:\APP\ORA11G\ORADATA\DB11\SYSTEM01.DBF'

resize 2M

ORA-3297 signalled during: alter database datafile 'C:\APP\ORA11G\ORADATA\DB11\SYSTEM01.DBF'

resize 2M...

Keep this error in mind and generate another, more serious error. A corrupt block is pretty serious, but how does the DBA get one when he does not really want one? Use BBED, the Block Browser and Editor tool, covered elsewhere in this book. Another way to corrupt a block, and to some degree a file, is to use a Hex editor such as UltraEdit. Take a cold backup, edit the USERS01 datafile by changing a character (look for OPERATIONS and change it to PPERATIONS while in Hex edit mode), save and close, then startup the database. The following error output should show up:

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1334380 bytes

Variable Size             205521812 bytes

Database Buffers          322961408 bytes

Redo Buffers                5844992 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'C:\APP\ORA11G\ORADATA\DB11\USERS01.DBF'

That is a bit more serious than a user error for a wrong size while resizing a datafile. Start ADRCI and set the home to the rdbms path, but leave all homes in the SHOW HOME output.

The next step is to see what SHOW INCIDENT displays. In the output below, a CREATE_TIME column is not shown due to editing/page size limitations.

adrci> set homepath diag\rdbms\db11\db11

adrci> show incident

ADR Home = c:\app\ora11g\diag\rdbms\db11\db11:

********************************************************

INCIDENT_ID          PROBLEM_KEY

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

8545                 ORA 600 [kcidr_io_check_common_6]

1 rows fetched

Note that the database did not and does not have to be opened or have an instance running, for that matter, in order to use ADRCI.

Pretending that one is clueless about what caused this error, or realistically, had it been a true ORA-00600 or ORA-07445 error that often times mandate getting assistance from Oracle Support Services, it is now time to generate a package and finalize it before uploading into a Service Request on MetaLink.

The problem has an identifier or problem key, which generally speaking is the error number and the error message. One needs to start a session and choose the relevant ADR Home. So look at some other useful commands along the way to creating the package.

Use the "show home" command to see which ADR homes are available, followed by the "set homepath" as necessary.  Note that more than one home can be involved in an incident. Only the rdbms home need apply in this example for viewing a specific file.

adrci> set homepath diag\rdbms\db11\db11

adrci> show home

ADR Homes:

diag\rdbms\db11\db11

With a specific home set, use ADRCI to view the tail of the XML-based alert log file. The output will have the XML stripped off. The syntax is:

show alert –tail [options]

The options are quite similar to UNIX. Using "tail –f" in an ADRCI session, one is able to view new entries into the log file as they occur. As an experiment, try using "show alert –tail –f" in an ADRCI session, and then in another session, switch the logfile via "alter system switch logfile" and the "Thread 1 advanced…" output appears in the ADRCI's session window.

Even more intuitive regarding available commands is spooling a session to a file. SPOOL ON <path/filename> and SPOOL OFF do the trick.

If it is known that there is a tracefile, or if one simply wants to view all tracefiles, the intuitive command of "show tracefile" will support that need. Yet as is well known, not all trace files are germane with respect to an incident.

Since there is an incident number, are there any trace files associated with it?

adrci> show tracefile -i 8545

     diag\rdbms\db11\db11\incident\incdir_8545\db11_m000_4844_i8545.trc

Yes, as luck has it, there is a trace file. Remember, that is luck only in the sense that one was needed for this example, but lucky in real life, no. What are the details about this incident?

ADRCI will list incident reports in a brief or detail mode, and also by a specific incident number in case there is more than one in the repository. Looking at the detailed output for this incident, the following comes up.

adrci> show incident -mode detail

ADR Home = c:\app\ora11g\diag\rdbms\db11\db11:

********************************************************************

**********************************************************

INCIDENT INFO RECORD 1

**********************************************************

   INCIDENT_ID                   8545

   STATUS                        ready

   CREATE_TIME                   2015-09-20 18:40:36.762000 -06:00

   PROBLEM_ID                    1

   CLOSE_TIME                    <NULL>

   FLOOD_CONTROLLED              none

   ERROR_FACILITY                ORA

   ERROR_NUMBER                  600

   ERROR_ARG1                    kcidr_io_check_common_6

   ERROR_ARG2                    4

   ERROR_ARG3                 C:\APP\ORA11G\ORADATA\DB11\USERS01.DBF

   ERROR_ARG4                    8192

   ERROR_ARG5                    2

   ERROR_ARG6                    4

   ERROR_ARG7                    <NULL>

   ERROR_ARG8                    <NULL>

   SIGNALLING_COMPONENT          <NULL>

   SIGNALLING_SUBCOMPONENT       <NULL>

   SUSPECT_COMPONENT             <NULL>

   SUSPECT_SUBCOMPONENT          <NULL>

   ECID                          <NULL>

   IMPACTS                       0

   PROBLEM_KEY                   ORA 600 [kcidr_io_check_common_6]

   FIRST_INCIDENT                8545

   FIRSTINC_TIME                 2015-09-20 18:40:36.762000 -06:00

   LAST_INCIDENT                 8545

   LASTINC_TIME                  2015-09-20 18:40:36.762000 -06:00

   IMPACT1                       0

   IMPACT2                       0

   IMPACT3                       0

   IMPACT4                       0

   KEY_NAME                      ProcId

   KEY_VALUE                     18.2

   KEY_NAME                      Client ProcId

   KEY_VALUE                     ORACLE.EXE.1116_4844

   KEY_NAME                      SID

   KEY_VALUE                     154.5

   OWNER_ID                      1

   INCIDENT_FILE      c:\app\ora11g\diag\rdbms\db11\db11\trace\db11_m000_4844.trc

   OWNER_ID                      1

   INCIDENT_FILE                 c:\app\ora11g\diag\rdbms\db11\db11\incident\ Ã

incdir_8545\db11_m000_4844_i8545.trc

1 rows fetched

Now it is time to package the incident. Packaging an incident is a three-step process. The main part of packaging is to go from the logical to the physical. Right now, there is metadata about the incident, so create a physical set of data, i.e a set of files which contains the incident information. The steps are:

1.      Create a logical package

2.      Add diagnostic information to the package

3.      Generate the physical package

The package can be based on one of the following attributes:

n  Empty (and add items in step 2)

n  Incident number

n  Problem number

n  Problem key

n  Time interval

Different failure scenarios justify the options. The same repeated error could be grouped together by the problem key as the occurrences will have different times and incident numbers. For multiple errors, a time interval would be the most convenient grouping set. There is an error but the DBA is not sure what to hone in on yet, so an empty package is created waiting for the input. For the very specific, as in the working example, an incident number fits.

The second step is optional if anything but the empty package option was chosen. Even if something else was chosen, this is when and where additional files can be added with the restriction that they reside in the ADR base directory hierarchy. It should go without saying that if an empty package is created, then Step 2 is mandatory. ADRCI uses commands to add files to packages.

Finally, generate the physical incident package. Just like in using the old RDA tool, the DBA is required to compress the files before uploading to MetaLink. In ADRCI, create full or incremental zip files as the contents of the package. Incremental files cannot be added unless there is a complete file present. Additionally, the files can also be sequenced. How are the files associated? They are associated by the leading part of the error number and a timestamp.

Use the IPS CREATE PACKAGE [options] command to create the physical package. Given that there is an incident number, use that as follows.

adrci> ips create package incident 8545

Created package 1 based on incident id 8545, correlation level typical

What showed up? There is now a logical container identified as package 1. Assuming that no additional files are being added (Step 2), the next step is to create the zip file by generating the package.

Using C:\temp because it is easy to navigate to, the following appears:

adrci> ips generate package 1 in c:\temp

Generated package 1 in file c:\temp\ORA600kci_20080920194531_COM_1.zip, mode complete

It is known that the incident was an ORA-00600 error with the time of package creation being September 20, 2015 around 1945 hours. It is a complete file (COM versus INC), and it is file number 1. As proof positive, the zip file exists in C:\TEMP.

So what is in the zip file? Extract it and see. What will be found is a copy of the folder hierarchy, some metadata, and copies of relevant files, which includes the trace file and the alert log.

With the zipped package, the DBA is now ready to provide Oracle support with the information they need to help. Not only does the service request process change a bit with this built-in fault diagnosis and reporting capability, so does the interface to MetaLink. The changeover is phased based on the products one uses, but as of September 20, 2015, MetaLink became "My Oracle Support" for many customers.

The classic MOSC interface can still be used for awhile, but get used to the Flash-based My Oracle Support process. It is quite sharp looking and one can tell a lot of work went into creating a more professional interface.