Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle ADRCI Tips


Oracle Database Tips by Donald Burleson

This is an excerpt from  Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Also see my related notes on ADR parameters.

 

The ADRCI commands are not too exotic and using SHOW, SET and HELP make the tool feel a lot like the listener control utility LSNRCTL. That is another advantage of ADRCI over Enterprise Manager: the commands in ADRCI are here and now. OEM has latency issues. How many times has one seen the big red down arrow indicating the instance is down while at the same time the DBA is logged in via SQL*Plus?

 

By means of a quick introduction to ADRCI, here is the start of a session, some "where am I" information, and an output of help topics.

C:\>adrci

ADRCI: Release 11.1.0.6.0 - Beta on Sat Sep 20 15:38:12 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.

ADR base = "c:\app\ora11g"

adrci> show home

ADR Homes:
diag\clients\user_unknown\host_411310321_11
diag\rdbms\db11\db11
diag\tnslsnr\t42\listener

adrci> help

 HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
        SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL

 There are other commands intended to be used directly by Oracle, type

 "HELP EXTENDED" to see the adrci command  list.

 

The hierarchy of directories within the repository is shown below (from Figure 8-1 in the Administrator's Guide).

 

Figure 8.2:  ADR Hierarchy

 

ADRCI offers the following features:

- Command-line interface with intuitive commands
- Readily accessible, does not rely on the database being open
- Automates the collection and packaging of many files
- Allows one to add one's own files
- Standardizes the content and exchange of information between you and Oracle Support

As has been seen, ADRCI is quick and easy to use. Plus, how to practice using the utility by creating one's own critical, but recoverable, error, has also been shown.

 

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.

 

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]


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

 

% Starting in release 11g, BACKGROUND_DUMP_DEST and USER_DUMP_DEST became deprecated. Both are replaced by the new DIAGNOSTIC_DEST parameter. The location is associated with ORACLE_BASE, if set, or ORACLE_HOME/log if not.

 

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.

 

To recap where one is so far in the process: the tool (ADRCI) has been identified, there is a problem (an ORA-00600 error), and there is an incident (only the critical error, not the user one, but an incident can have more than one error associated with it).

 

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.

 

% Despite what Oracle's documentation says, using Control-C to end the live monitoring will not only end the tailing of the file, it will also terminate the ADRCI session.

 

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.

 

Figure 8.3: Packaged Incident File

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.

 

Do not forget to restore the cold backup. A working database is needed for the next topic.

 




NOTE: Rampant author Laurent Schneider has some additional insight into creating an Oracle Automatic Diagnostic Repository (ADR)



Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo,  Donald Burleson, and Steve Callan). 

Buy direct from the publisher and save 30%!

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.