Hello world!

This is your very first post. Click the Edit link to modify or delete it, or start a new post. If you like, use this post to tell readers why you started this blog and what you plan to do with it.

Happy blogging!

Advertisements

Automatic Storage Management (ASM)

Automatic Storage Management (ASM) FAQ

We have tried to consolidate list of Frequently Asked Questions (FAQ) on ASM. Please feel free to ask any other question or contribute to this FAQ by using comment section

Frequently Asked Question on ASM

1) What is ASM?

Automatic storage Management (ASM) is a new type of filesystem which was introduced with Oracle 10g. ASM is recommended filesystem for RAC and Single instance ASM for storing database files. This provides direct I/O to the file and performance is comparable with that provided by RAW Devices. Oracle creates a separate instance for this purpose.

2) How do we identify if we are connected to Normal Instance or ASM instance?

Issue following command to identify this

<span style=”font-size: small; font-family: arial,helvetica,sans-serif;”> SQL&gt; show parameter instance_type</span> <span style=”font-size: small; font-family: arial,helvetica,sans-serif;”>NAME TYPE VALUE ———————————— ———– —————————— instance_type string asm</span>

In case you are connected to ASM instance, it will display value as asm, otherwise it will display value as RDBMS.

3) What are Diskgroup’s and Failuregroups?

Diskgroup is a terminology used for logical structure which holds the database files. Each Diskgroup consists of Disks/Raw devices where the files are actually stored. Any ASM file is completely contained within a single disk group. However, a disk group might contain files belonging to several databases and a single database can use files from multiple disk groups.

Failuregroups are used when using Normal/High Redundancy. They contain the mirrored ASM extents and must be containing different disks and preferably on separate disk controller.

4)Can ASM be used as replacement for RAID?

ASM is supposed to stripe the data and also mirror the data (if Using Normal, High Redundancy). So this can be used as a alternative for RAID 0+1 solutions

5) How does ASM provides Redundancy?

When you create a disk group, you specify an ASM disk group type based on one of the following three redundancy levels:

  • Normal for 2-way mirroring – When ASM allocates an extent for a normal redundancy file, ASM allocates a primary copy and a secondary copy. ASM chooses the disk on which to store the secondary copy in a different failure group other than the primary copy.
  • High for 3-way mirroring. In this case the extent is mirrored across 3 disks.
  • External to not use ASM mirroring. This is used if you are using Third party Redundancy mechanism like RAID, Storage arrays.

6) Can we change the Redundancy for Diskgroup after its creation.

No, we cannot modify the redundancy for Diskgroup once it has been created. To alter it we will be required to create a new Diskgroup and move the files to it. This can also be done by restoring full backup on the new Diskgroup. Following metalink note describes the steps

Note.438580.1 – How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy)

7) I am unable to open the ASM instance. What is the reason?

ASM instance does not have open stage. It has got only two options

  • Nomount- This starts the ASM instance
  • Mount- At this stage, Diskgroup defined in ASM_DISKGROUPS parameter are mounted

When you try to open the ASM instance , you get following error

<span style=”font-size: small; font-family: arial,helvetica,sans-serif;”>SQL&gt; alter database open; alter database open * ERROR at line 1: ORA-15000: command disallowed by current instance type</span>

8)Can ASM instance and database (rdbms) be on different servers?

ASM instance and Database (rdbms) have to be present on same server. Otherwise it will not work.
9)Can we see the files stored in the ASM instance using standard unix commands.

No, you cannot see the files using standard unix commands like ls. You need to use utility called asmcmd to do this. This is present in 10.2 and above.e.g
/home/oracle>asmcmd
Asmcmd>

You can use help command to see the options.

<span style=”font-size: small; font-family: arial,helvetica,sans-serif;”>Note: – You can use asmcmd for 10.1 database also. For this you can copy the </span><span style=”font-size: small; font-family: arial,helvetica,sans-serif;”>asmcmdcore and asmcmd file from 10.2 ORACLE_HOME to 10.1 ORACLE_HOME. Else you can download the files from Metalink </span><span style=”font-size: small; font-family: arial,helvetica,sans-serif;”><strong>Note:332853.1</strong></span>

10) What kind of files can be stored on ASM Diskgroup.

You can store the following file types in ASM disk groups:

  • Control files
  • Datafiles, temporary datafiles, and datafile copies
  • SPFILEs
  • Online redo logs, archive logs, and Flashback logs
  • RMAN backups
  • Disaster recovery configurations
  • Change tracking bitmaps
  • Data Pump dumpsets

Note: Oracle executables and ASCII files, such as alert logs and trace files, cannot be stored in ASM disk groups.

11)Can we use ASM for storing Voting Disk/OCR in a RAC instance?

No. You cannot use ASM for storing the voting disk and OCR. It is due to the fact that Clusterware starts before ASM instance and it should be able to access these files which is not possible if you are storing it on ASM. You will have to use raw devices or OCFS or any other shared storage.

12)Does ASM instance automatically rebalances and takes care of hot spots?

No. This is a myth and ASM does not do it. It will initiate automatic rebalance only when a new disk is added to Diskgroup or we drop a disk from existing Diskgroup.

13) What is ASMLIB?
ASMLIB is the support library for the ASM. ASMLIB allows an Oracle database using ASM more efficient and capable access to diskgroups. The purpose of ASMLIB, is to provide an alternative interface to identify and access block devices. Additionally, the ASMLIB API enables storage and operating system vendors to supply extended storage-related features. You can read more about ASMLIB in

Note: 359266.1 – FAQ ASMLIB CONFIGURE, VERIFY, TROUBLESHOOT

ASMLIB for linux can be downloaded from following link

http://www.oracle.com/technology/tech/linux/asmlib/index.html

14) What is SYSASM role?
Starting from Oracle 11g, SYSASM role can be used to administer the ASM instances. You can continue using SYSDBA role to connect to ASM but it will generate following warning messages at time of startup/shutdown, create Diskgroup/add disk ,etc

<span style=”font-size: small; font-family: arial,helvetica,sans-serif;”>Alert entry WARNING: Deprecated privilege SYSDBA for command ‘STARTUP'</span>

15) How can we copy the files from/to ASM?

You can use RMAN or DBMS_FILE_TRANSFER.COPY_FILE procedure to copy the files to/from ASM from/to Filesystem. Starting from Oracle 11g, you can use cp command in asmcmd to perform the same between ASM Diskgroups and also to OS Filesystem.

16) Can we use BCV to clone the ASM Diskgroup on same host?

Diskgroup Cloning is not supported on the same host using BCV. You have no other option to use except RMAN DUPLICATE. You can find more detail on BCV and ASM in below whitepaper

http://www.oracle.com/technology/products/database/asm/pdf/asm-on-emc-5_3.pdf

17) Can we edit the ASM Disk header to change the Diskgroup Name?

No. This cannot be done. Please find details in

http://askdba.org/weblog/?p=128

18) Whats is Kfed?

kfed is a utility which can be used to view the ASM Disk information. Syntax for using it is

kfed read devicename

Read more about kfed here.

19) Can we use block devices for ASM Disks?

Yes. Starting from Oracle Database 10.2 block devices can be used directly for ASM Disks in Linux. This is not true for other Unix based systems where block devices are not supported yet.

Along with this it is recommended to use a Device mapping functionality so that disk mapping is preserved after disk failure. This is important when you have devices as /dev/sda,/dev/sdb,/dev/sdc and due to some reason the devices are not detected at next reboot (say /dev/sdb), the system will map the incorrect device (i.e /dev/sdc will be marked as /dev/sdb). You can use following methods for preserving disk names

-udev – the role of udev is to provide device persistency and naming consistency.This is especially important for the Oracle Cluster Registry (OCR) and Voting disks required by Oracle Clusterware.

ASMLIB – ASMLIB will provide device management specifically for ASM disk devices.

Refer to following Whitepaper for more details

http://www.oracle.com/technology/products/database/asm/pdf/device-mapper-udev-crs-asm%20rh4.pdf

20)Is it mandatory to use disks of same size and characteristics for Diskgroups?

No, it is not mandatory to use the disks of same size and characteristics for Diskgroups though it is a Recommended Practice.

Same size disk for Failuregroups in Normal/High redundancy will prevent issues like ORA-15041 as the file extents needs to be mirrored across the disks. Also as Oracle distributes data based on capacity, so larger disk will have more data stored in it and which will result in higher I/O to disk and eventually can lead to sub-optimal performance.

Moreover having disks of different characteristic like varying disk speed can impact the performance.

When managing disks with different size and performance capabilities, best practice is to group them into disk groups according to their characteristics. So you can use higher speed disks for your database files while other disks can be part of Diskgroup used for Flash Recovery Area.

21)Do we need to install ASM and Oracle Database Software in different ORACLE_HOME?

No. Again installing ASM and Oracle Database Software in different ORACLE_HOME is not mandatory but a best practice. This is useful in cases when we need to have multiple databases using same ASM instance and you need to patch only one of them. E.g You need to apply a CBO patch to one of 10.2 database while your other 10.1 database using different installation does not require it. In this case having a ASM_HOME separate from 10.2 ORACLE_HOME will allow your 10.1 database to keep running. Thus this approach is useful for High Availability.

22)What is the maximum size of Disk supported by ASM?

ASM supports disks upto 2Tb, so you need to ensure that lun size should be less then 2Tb. 10.2.0.4 and 11g database will give error if you try to create a diskgroup with ASM disks having disk size >2Tb.

23)I have created Oracle database using DBCA and having a different home for ASM and Oracle Database. I see that listener is running from ASM_HOME. Is it correct?

This is fine. When using different home for ASM, you need to run the listener from ASM_HOME instead of ORACLE_HOME.

11gR2 – SysAsm vs SysDba

SYSASM role was introduced in 11gR1 and was designed to administer ASM instances. In 11gR1 , if you connected with SYSDBA role , you used to get a warning which was only recorded in alert log (Refer to my earlier post here). But things have changed in 11gR2. While trying to dismount a Diskgroup, I found following errors

SQL> alter diskgroup flash_arc mount;
alter diskgroup flash_arc mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group

Above error indicates that I do not have permission on the ASM Diskgroup.  As per 11gR2 documentation, SYSASM privilege is used for carrying out administration tasks on ASM Diskgroups. SYSDBA privilege can be used only for creating/deleting aliases and querying ASM dictionary views.  Frankly speaking, you should unlearn the habit of connecting as “/ as sysdba” to AS

Can ASM DiskGroup Be Renamed?

Answer is NO. It is not possible to rename the diskgroup by editing the ASM disk header. kfed is known to be used for patching ASM disk headers for corruption (only oracle support can do it) and for viewing ASM header contents. Only way to change this by dropping and recreating the diskgroup.

In case you wish to create a new diskgroup with a name say +DG1 which was being used by a Diskgroup which is not mounted (Have some ASM Disk members still in ASM_DISKSTRING path), then you would face following error

    <strong>ORA-15030</strong>: diskgroup name "string" is in use by another diskgroup
    <strong>Cause:</strong> A CREATE DISKGROUP command specified a Diskgroup name that was already assigned to another diskgroup.
    <strong>Action: </strong>Select a different name for the Diskgroup.

In case you wish to create the Diskgroup with same name +DG1 you will be required to clear the ASM disk header using

dd if=/dev/zero of=/dev/raw/raw11 bs=1024 count=100

After this you can recreate the Diskgroup with same name.

M instance and learn connecting as “/ as sysasm”

Ref:http://askdba.org/weblog/2009/11/11gr2-sysasm-vs-sysdba/

ADRCI (Automatic Diagnostic Repository Command Interpreter)

Automatic Diagnostic Repository (ADR) in Oracle Database 11g Release 1 (ADRCI)

The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for handling diagnostic information.

Related articles.

Organisation

The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for diagnostic information, made up of a directory structure like the following.

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/alert
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/cdump
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/hm
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/incident
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/trace
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/{others}

Typical installations will have the ADR_BASE set to the ORACLE_BASE. For example, a database called “orcl” might have a structure like the following.

/u01/app/oracle/diag/rdbms/orcl/orcl/alert
/u01/app/oracle/diag/rdbms/orcl/orcl/cdump
/u01/app/oracle/diag/rdbms/orcl/orcl/hm
/u01/app/oracle/diag/rdbms/orcl/orcl/incident
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

The locations of the various diagnostics directories can be displayed using the V$DIAG_INFO view.

COLUMN name FORMAT A25
COLUMN value FORMAT A65

SELECT name, value FROM v$diag_info;

NAME                      VALUE
------------------------- -----------------------------------------------------------------
Diag Enabled              TRUE
ADR Base                  /u01/app/oracle
ADR Home                  /u01/app/oracle/diag/rdbms/orcl/orcl
Diag Trace                /u01/app/oracle/diag/rdbms/orcl/orcl/trace
Diag Alert                /u01/app/oracle/diag/rdbms/orcl/orcl/alert
Diag Incident             /u01/app/oracle/diag/rdbms/orcl/orcl/incident
Diag Cdump                /u01/app/oracle/diag/rdbms/orcl/orcl/cdump
Health Monitor            /u01/app/oracle/diag/rdbms/orcl/orcl/hm
Default Trace File        /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19975.trc
Active Problem Count      0
Active Incident Count     0

11 rows selected.

SQL>

Setting the ADR Location

For the database, the ADR location is specified using the diagnostic_dest initialisation parameter.

SQL> ALTER SYSTEM SET diagnostic_dest='/u01/app/oracle';

System altered.

SQL> show parameter diagnostic_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle/
SQL>

For the listener, the ADR location is set by editing the “$ORACLE_HOME/network/admin/listener.ora” file to include the following entry.

ADR_BASE_LISTENER = /u01/app/oracle

The listener must be restarted for the change to take effect.

$ lsnrctl stop
$ lsnrctl start

If the “diag” directory is not already present, it will be created when the listener or database are started with the new setting.

ADR Command Interpreter (ADRCI)

You can navigate the contents of ADR using operating systems command line or file browsing tools, but Oracle also provide the ADR Command Interpreter (ADRCI), which is probably a better solution for many tasks.

Assuming your environment is set correctly, you should be able to start ADRCI by issuing the “adrci” command.

$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Mon Jun 9 13:41:29 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci>

When started, you should see the “ADR base =” message. If instead you get a message saying no base is set, you can manually set it using the SET BASE command.

adrci> set base /u01/app/oracle

Typing the HELP command provides a list of help topics related to available commands.

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 list

adrci>

As the help suggests, you can drill down by asking for help on specific commands.

adrci> help show alert

  Usage: SHOW ALERT [-p <predicate_string>]  [-term]
                    [ [-tail [num] [-f]] | [-file <alert_file_name>] ]
  Purpose: Show alert messages.

  Options:
    [-p <predicate_string>]: The predicate string must be double-quoted.
    The fields in the predicate are the fields:
        ORIGINATING_TIMESTAMP         timestamp
        NORMALIZED_TIMESTAMP          timestamp
        ORGANIZATION_ID               text(65)
        COMPONENT_ID                  text(65)
        HOST_ID                       text(65)
        HOST_ADDRESS                  text(17)
        MESSAGE_TYPE                  number
        MESSAGE_LEVEL                 number
        MESSAGE_ID                    text(65)
        MESSAGE_GROUP                 text(65)
        CLIENT_ID                     text(65)
        MODULE_ID                     text(65)
        PROCESS_ID                    text(33)
        THREAD_ID                     text(65)
        USER_ID                       text(65)
        INSTANCE_ID                   text(65)
        DETAILED_LOCATION             text(161)
        UPSTREAM_COMP_ID              text(101)
        DOWNSTREAM_COMP_ID            text(101)
        EXECUTION_CONTEXT_ID          text(101)
        EXECUTION_CONTEXT_SEQUENCE    number
        ERROR_INSTANCE_ID             number
        ERROR_INSTANCE_SEQUENCE       number
        MESSAGE_TEXT                  text(2049)
        MESSAGE_ARGUMENTS             text(129)
        SUPPLEMENTAL_ATTRIBUTES       text(129)
        SUPPLEMENTAL_DETAILS          text(129)
        PROBLEM_KEY                   text(65)

    [-tail [num] [-f]]: Output last part of the alert messages and
    output latest messages as the alert log grows. If num is not specified,
    the last 10 messages are displayed. If "-f" is specified, new data
    will append at the end as new alert messages are generated.

    [-term]: Direct results to terminal. If this option is not specified,
    the results will be open in an editor.
    By default, it will open in emacs, but "set editor" can be used
    to set other editors.

    [-file <alert_file_name>]: Allow users to specify an alert file which
    may not be in ADR. <alert_file_name> must be specified with full path.
    Note that this option cannot be used with the -tail option

  Examples:
    show alert
    show alert -p "message_text like '%incident%'"
    show alert -tail 20

adrci>

Viewing the Alert Log

Depending on your setup, you may have multiple homes (Database, Listener, Grid Infrastructure etc.). It is important you point to the correct home before issuing any commands.

adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl
diag/tnslsnr/ol6-112/listener
adrci> set home diag/rdbms/orcl/orcl
adrci>

With the home set, we can look at the contents of the alert log. The help text for the SHOW ALERT command provides some examples.

Examples:
  show alert
  show alert -p "message_text like '%incident%'"
  show alert -tail 20

We might decide to look for errors in the alert log with a command like the following.

adrci> show alert -p "message_text like '%ORA-%'"

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
Output the results to file: /tmp/alert_1781_13990_orcl_1.ado
adrci>

We could also do a UNIX-style “tail -f” of the alert log using the following command, even on Windows.

adrci> show alert -tail -f

Viewing Trace Files

The alert log will often make reference to trace files. These can be viewed directly using the SHOW TRACE command.

adrci> show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc

Managing Diagnostic Information (Purging Trace Files)

The PURGE command can be used to remove some or all of the diagnostic information from the repository. The HELP PURGE command describes the usage.

adrci> help purge

  Usage: PURGE [[-i <id1> | <id1> <id2>] |
               [-age <mins> [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]]]:

  Purpose: Purge the diagnostic data in the current ADR home. If no
           option is specified, the default purging policy will be used.

  Options:
    [-i id1 | id1 id2]: Users can input a single incident ID, or a
    range of incidents to purge.

    [-age <mins>]: Users can specify the purging policy either to all
    the diagnostic data or the specified type. The data older than <mins>
    ago will be purged

    [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]: Users can specify what type of
    data to be purged.

  Examples:
    purge
    purge -i 123 456
    purge -age 60 -type incident

adrci>

So to purge all diagnostic information, including trace files, older than 1 month you would issue the following.

adrci> purge -age 43200
adrci>

Problems and Incidents

When a problem occurs on the database, it is logged in the alert log and an incident is created. Doing a search of the alert log with the following command will reveal some information about the incident.

adrci> show alert -p "message_text like '%incident%'"

2013-10-09 10:05:17.154000 +01:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18310.trc  (incident=72697):
ORA-07445: exception encountered: core dump [kghalo()] [SIGSEGV] [ADDR:0x9FFFFFFFBFFFF000]
  [PC:0x40000000065AE680] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2013-10-09 10:05:36.501000 +01:00
Dumping diagnostic data in directory=[cdmp_20131009100536], requested by (instance=1, osid=18310), summary=[incident=72697].

A simpler solution is to display the problem from the command line directly using the SHOW PROBLEM command.

adrci> show problem

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 3137 [3120]                                             71593                2013-08-09 10:16:43.714000 +01:00
2                    ORA 7445 [kghalo()]                                         72697                2013-10-09 10:05:17.154000 +01:00
2 rows fetched

adrci>

The same problem can occur multiple times, so a single problem may result in multiple incidents. Incidents are displayed using the SHOW INCIDENT command.

adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
71593                ORA 3137 [3120]                                             2013-08-09 10:16:43.714000 +01:00
72697                ORA 7445 [kghalo()]                                         2013-10-09 10:05:17.154000 +01:00
2 rows fetched

adrci>

Once you’ve identified an incident of interest, you can look at it in more detail by altering the mode in the SHOW INCIDENT command.

adrci> show incident -mode detail -p "incident_id=72697"

ADR Home = /u01/app/oracle/diag/rdbms/lstu/lstu:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   72697
   STATUS                        ready
   CREATE_TIME                   2013-10-09 10:05:17.154000 +01:00
   PROBLEM_ID                    2
   CLOSE_TIME                    <NULL>
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    kghalo()
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0x9FFFFFFFBFFFF000
   ERROR_ARG4                    PC:0x40000000065AE680
   ERROR_ARG5                    Address not mapped to object
   ERROR_ARG6                    <NULL>
   ERROR_ARG7                    <NULL>
   ERROR_ARG8                    <NULL>
   ERROR_ARG9                    <NULL>
   ERROR_ARG10                   <NULL>
   ERROR_ARG11                   <NULL>
   ERROR_ARG12                   <NULL>
   SIGNALLING_COMPONENT          <NULL>
   SIGNALLING_SUBCOMPONENT       <NULL>
   SUSPECT_COMPONENT             <NULL>
   SUSPECT_SUBCOMPONENT          <NULL>
   ECID                          <NULL>
   IMPACTS                       0
   PROBLEM_KEY                   ORA 7445 [kghalo()]
   FIRST_INCIDENT                72697
   FIRSTINC_TIME                 2013-10-09 10:05:17.154000 +01:00
   LAST_INCIDENT                 72697
   LASTINC_TIME                  2013-10-09 10:05:17.154000 +01:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      ProcId
   KEY_VALUE                     287.6
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@biro01 (TNS V1-V3).18310_1
   KEY_NAME                      ECID
   KEY_VALUE                     55774dc6ecfa57a3:2a5146fe:13ffc25ce34:-8000-0000000000055601.1
   KEY_NAME                      PQ
   KEY_VALUE                     (0, 1381309514)
   KEY_NAME                      SID
   KEY_VALUE                     1125.45841
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/lstu/trace/orcl_ora_18310.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc
1 rows fetched

adrci>

Creating Packages to Send to Oracle Support

If you can’t solve the problem yourself, you can use the Incident Packaging Service (IPS) to gather all pertinent information so it can be sent to Oracle Support. This should reduce the amount of time you waste trying to identify what information is necessary for them to identify and solve the problem.

First, create the package using the problem ID displayed by the SHOW PROBLEM command.

adrci> ips create package problem 2 correlate all
Created package 1 based on problem id 2, correlation level all
adrci>

Next, create a zip to send to Oracle Support by specifying the package number displayed by the above command.

adrci> ips generate package 1 in "/tmp"
Generated package 1 in file /tmp/IPSPKG_20140610100342_COM_1.zip, mode complete
adrci>

The package is now zipped and ready to upload to Oracle Support.

$ ls /tmp/IPSPKG*.zip
/tmp/IPSPKG_20140610100342_COM_1.zip
$

Cloud Control Integration

Incident management is integrated into Enterprise Manager Cloud Control. Navigate to the database of interest, the use the “Oracle Database > Diagnostics > Support Workbench” menu option. Incidents can be selected, packaged and uploaded directly to Oracle Support from Cloud Control.

Ref:http://oracle-base.com/articles/11g/automatic-diagnostics-repository-11gr1.php

Resolve huge archive gap between PRIMARY and STANDBY

Resolve huge archive gap between PRIMARY and STANDBY DATAGUARD

A Physical Standby database synchs with Primary by continuous apply of archive logs from a Primary Database.

When the logs are missing on standby difference is huge (say more than 500 logs), you have to rebuild the standby database from scratch.

With out rebuild standby database, As an enhancement from 10g, an incremental backup created with BACKUP INCREMENTAL… FROM SCN can be used to refresh the standby database.

Please use below query to find out archive gap on Standby:

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
 
 Thread        Last Sequence Received      Last Sequence Applied      Difference
———-              ———————-                  ———————             ———-  
1                               8254                                      7954                          300
 

Find the SCN on the PRIMARY:

SQL> select current_scn from v$database;
 
CURRENT_SCN
———–  
242671761

Find the SCN on the STANDBY:

 
SQL> select current_scn from v$database;
CURRENT_SCN
———–  
223771173

Clearly you can see there is difference

Stop and shutdown the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

Shutdown the standby database

SQL> shut immediate

On the primary, take an incremental backup from the SCN number where the standby current value 223771173:

 RMAN> run { allocate channel c1 type disk format ‘/backup/%U.bkp’;
backup incremental from scn 223771173 database;
 }

On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as ‘/backup/for_standby.ctl’;

Database altered.

Copy the standby controlfile to STANDBY and bring up the standby instance in nomount status with standby controlfile:

SQL> startup nomount

SQL> alter database mount standby database;

Connect to RMAN on STANDBY, Catalog backup files to RMAN using below commands:

$ rman target=/

RMAN> catalog start with ‘/backup’;

PERFORM RECOVER:

RMAN> recover database;

Start managed recovery process:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Check the SCN’s in primary and standby it should be close to each other.

If archive gap difference is minimal or less, Please check this

SIMPLE DATABASE CREATION

SIMPLE DATABASE CREATION

CREATION OF DATABASE IN COMMAND LINE:

1) Set Environment file:

#su – oracle

$pwd

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/PRODuct/11.2.0/db_1

export PATH=$ORACLE_HOME/bin:$PATH

2) Create Parameter file:

$cd $ORACLE_HOME/dbs

$cpinit.ora initPROD.ora

vi $ORACLE_HOME/dbs/initPROD.ora

db_name = PROD

control_files=/u01/PROD/control1.ctl,/u01/PROD/control2.ctl

undo_management = AUTO

undo_tablespace = UNDOTBS1

diagnostic_dest=/u01/PROD/

:wq!

3) Start the instance:

Edit the file $ORACLE_HOME/sqlplus/admin/glogin.sql and add in line as below.
setsqlprompt “_USER’@’_CONNECT_IDENTIFIER> ”

sqlplus / as sysdba

startup nomount

Note : In the nomount state oracle reads all initialization parameter values from the pfile(initPROD.ora)

4) Create the database:

vi create.sql

create database test

logfile group 1 (‘/u01/PROD/redo1.log’) size 100M,

group 2 (‘/u01/PROD/redo2.log’) size 100M,

group 3 (‘/u01/PROD/redo3.log’) size 100M

datafile ‘/u01/PROD/system.dbf’ size 500M autoextend on next 10M maxsize unlimited extent management local

sysauxdatafile ‘/u01/PROD/sysaux.dbf’ size 100M autoextend on next 10M maxsize unlimited

undotablespace undotbs1 datafile ‘/u01/PROD/undotbs1.dbf’ size 100M

default temporary tablespace temp tempfile ‘/u01/PROD/temp01.dbf’ size 100M;

sqlplus / as sysdba

@create.sql

5) Run catalog and catproc:

After Successful creation of the database we need to execute catalog.sql and catproc.sql.These two scripts updates the data dictionary tables and views. And pupbld.sql must be executed from Systemuser.This script updates users product information.

sqlplus / as sysdba

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

connect system/manager

@?/sqlplus/admin/pupbld.sql

####### Use Below dictionary view to see database information #####

sys@PROD>select * from v$version;

sys@PROD>select * from v$tablespace;

sys@PROD>select name from v$datafile;

sys@PROD>select name from v$tempfile;

sys@PROD>select name from v$controlfile;

sys@PROD>set wrap off

sys@PROD>select * from v$logfile;

Oracle – ORA-19502, ORA-27072 During RMAN Backup

While executing and RMAn backup the following error occurs:

channel ORA_DISK_1: starting piece 1 at 17-JUN-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 06/17/2010 03:27:42
ORA-19502: write error on file "/ocfs2/data07/oracle/oraback/OASSTBY/rman/OASLIVE_20100617_9072_1.rbak", blockno 8842241 (blocksize=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 9: Bad file descriptor
Additional information: 4
Additional information: 8842241
Additional information: 159744
Recovery Manager complete.

If you review the “disk free” section from the RDA report, the mount point will show as almost full. You can also check this from a df on Linux. The backup will be attempting to write to a larger disk space than is available.

The reason for this error is tha there is not enough disk space on the drive to complete the backup.

To resolve this error, clean up unnecessary files on the disk, or add disk space to the disk then re-run the backup.

Receiving “Internal Error has occurred. Check the log file for details” while accessing Oracle 11g Enterprise Manage DBConsole

The error occurs because the URL accessed is not correct one. In general the format would look like following:

http://{hostname}:{port}/em/console

https://{hostname}:{port}/em/console

Usually this URL always generates this error. The URL that should be used is:-

http://{hostname}:{port}/em

or

https://{hostname}:{port}/em

 Note that there is no ‘/console’ at the end.