Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching


当前位置: 首页 » 技术文章 » AODU

aodu(At Oracle Database Utility)之asm(二)

AODU> asm script    ---收集ASM信息,用于故障诊断
        ****Generic ASM metadata****
        SPOOL ASM<#>_GENERIC_ASM_METADATA.html
        -- ASM VERSIONS 10.1, 10.2, 11.1,  11.2 & 12.1
        SET MARKUP HTML ON
        SET ECHO ON
        SET PAGESIZE 200
        ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
        SELECT 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , SYSDATE " "  FROM DUAL;
        SELECT 'INSTANCE NAME: ==)> ' , INSTANCE_NAME " " FROM V$INSTANCE;
        SELECT 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " FROM V$SESSION WHERE PROGRAM LIKE '%SMON%';
        SELECT * FROM V$INSTANCE;
        SELECT * FROM GV$INSTANCE;
        SELECT * FROM V$ASM_DISKGROUP;
        SELECT * FROM  V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
        SELECT SUBSTR(D.NAME,1,16) AS ASMDISK, D.MOUNT_STATUS, D.STATE,
             DG.NAME AS DISKGROUP FROM V$ASM_DISKGROUP DG, V$ASM_DISK D
             WHERE DG.GROUP_NUMBER = D.GROUP_NUMBER;
        SELECT * FROM V$ASM_CLIENT;
         SELECT DG.NAME AS DISKGROUP, SUBSTR(C.INSTANCE_NAME,1,12) AS INSTANCE,
            SUBSTR(C.DB_NAME,1,12) AS DBNAME, SUBSTR(C.SOFTWARE_VERSION,1,12) AS SOFTWARE,
            SUBSTR(C.COMPATIBLE_VERSION,1,12) AS COMPATIBLE
            FROM V$ASM_DISKGROUP DG, V$ASM_CLIENT C
            WHERE DG.GROUP_NUMBER = C.GROUP_NUMBER;
        SELECT * FROM V$ASM_ATTRIBUTE;
        SELECT * FROM V$ASM_OPERATION;
        SELECT * FROM GV$ASM_OPERATION;
        SELECT * FROM V$VERSION;
        SELECT * FROM   V$ASM_ACFSSNAPSHOTS;
        SELECT * FROM   V$ASM_ACFSVOLUMES;
        SELECT * FROM   V$ASM_FILESYSTEM;
        SELECT * FROM   V$ASM_VOLUME;
        SELECT * FROM   V$ASM_VOLUME_STAT;
        SELECT * FROM   V$ASM_USER;
        SELECT * FROM   V$ASM_USERGROUP;
        SELECT * FROM   V$ASM_USERGROUP_MEMBER;
        SELECT * FROM   V$ASM_DISK_IOSTAT;
        SELECT * FROM   V$ASM_DISK_STAT;
        SELECT * FROM   V$ASM_DISKGROUP_STAT;
        SELECT * FROM   V$ASM_TEMPLATE;
        SHOW PARAMETER
        SHOW SGA
        --DISPLAYS INFORMATION ABOUT THE CONTENTS OF THE SPFILE.
        SELECT *  FROM  V$SPPARAMETER  ORDER BY 2;
        SELECT *  FROM  GV$SPPARAMETER  ORDER BY 3;
        --DISPLAYS INFORMATION ABOUT THE INITIALIZATION PARAMETERS THAT ARE CURRENTLY IN EFFECT IN THE INSTANCE.
        SELECT *  FROM  V$SYSTEM_PARAMETER ORDER BY 2;
        SELECT *  FROM  GV$SYSTEM_PARAMETER ORDER BY 3;
        -- 12C ACFS VIEWS
        SELECT * FROM  V$ASM_ACFS_ENCRYPTION_INFO;
        SELECT * FROM  V$ASM_ACFSREPL;
        SELECT * FROM  V$ASM_ACFSREPLTAG;
        SELECT * FROM  V$ASM_ACFS_SEC_ADMIN;
        SELECT * FROM  V$ASM_ACFS_SEC_CMDRULE;
        SELECT * FROM  V$ASM_ACFS_SEC_REALM;
        SELECT * FROM  V$ASM_ACFS_SEC_REALM_FILTER;
        SELECT * FROM  V$ASM_ACFS_SEC_REALM_GROUP;
        SELECT * FROM  V$ASM_ACFS_SEC_REALM_USER;
        SELECT * FROM  V$ASM_ACFS_SEC_RULE;
        SELECT * FROM  V$ASM_ACFS_SEC_RULESET;
        SELECT * FROM  V$ASM_ACFS_SEC_RULESET_RULE;
        SELECT * FROM  V$ASM_ACFS_SECURITY_INFO;
        SELECT * FROM  V$ASM_ACFSTAG;
        -- 12C ASM AUDIT VIEWS
        SELECT * FROM  V$ASM_AUDIT_CLEAN_EVENTS;
        SELECT * FROM  V$ASM_AUDIT_CLEANUP_JOBS;
        SELECT * FROM  V$ASM_AUDIT_CONFIG_PARAMS;
        SELECT * FROM  V$ASM_AUDIT_LAST_ARCH_TS;
        -- 12C ASM ESTIMATE VIEW
        SELECT * FROM  V$ASM_ESTIMATE;
        SELECT * FROM  GV$ASM_ESTIMATE;
        SPOOL OFF
        exit

        ****ASM Files & ASM Alias****
        spool ASM<#>_Alias_Files.html
        -- ASM Versions 10.1, 10.2, 11.1  & 11.2
        SET MARKUP HTML ON
        set echo on pagesize 200
        COLUMN BYTES FORMAT  9999999999999999
        alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
        select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " "  from dual;
        select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
        select * from v$asm_alias;
        select * from v$asm_file;
        show parameter asm
        show parameter cluster
        show parameter instance_type
        show parameter instance_name
        show parameter spfile
        show sga
        spool off
        exit

        ****ASM Full Path Alias Directory****
        spool Asm<#>_Full_Path_Alias_Directory.html
        -- ASM Versions 10.1, 10.2, 11.1  & 11.2
        SET MARKUP HTML ON
        set echo on pagesize 200
        alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
        select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " "  from dual;
        select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
        SELECT CONCAT('+'||GNAME, SYS_CONNECT_BY_PATH(ANAME, '/'))
         FULL_PATH, SYSTEM_CREATED, ALIAS_DIRECTORY, FILE_TYPE
         FROM ( SELECT B.NAME GNAME, A.PARENT_INDEX PINDEX,
         A.NAME ANAME, A.REFERENCE_INDEX RINDEX,
         A.SYSTEM_CREATED, A.ALIAS_DIRECTORY,
         C.TYPE FILE_TYPE
         FROM V$ASM_ALIAS A, V$ASM_DISKGROUP B, V$ASM_FILE C
         WHERE A.GROUP_NUMBER = B.GROUP_NUMBER
         AND A.GROUP_NUMBER = C.GROUP_NUMBER(+)
         AND A.FILE_NUMBER = C.FILE_NUMBER(+)
         AND A.FILE_INCARNATION = C.INCARNATION(+) )
         START WITH (MOD(PINDEX, POWER(2, 24))) = 0
         CONNECT BY PRIOR RINDEX = PINDEX;
        spool off
        exit

        ****ASMCMD commands to gather complementary metadata information****
        ################################################################################################
        ### Script Name:  asmcmd_script.sh   ###
        ################################################################################################
        ###  The next script generates additional ASM metadata information thru the ASMCMD interface ###
        ################################################################################################
        echo "ASMCMD commands to gather complementary metadata information:" > /tmp/asmcmd_script.out 2> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
        asmcmd -p ls -ls   >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p lsattr   >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p lsct     >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p lsdg  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p lsdsk >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p lsof  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p lsod  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p iostat        >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p dsget >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p lsop  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p spget >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p  lstmpl       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p   lsusr       >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p  lsgrp        >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p   lspwusr     >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        asmcmd -p   volinfo -a  >> /tmp/asmcmd_script.out               2>> /tmp/asmcmd_script.out
        echo "=================================="       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        echo "                                  "       >> /tmp/asmcmd_script.out  2>> /tmp/asmcmd_script.out
        ##############################################################################################################
        Note:On Windows environments the "2> /tmp/asmcmd_script.out" or "2>> /tmp/asmcmd_script.out" statements is not required,thus it can be omitted.
AODU>

AODU> asm passwd   ---如何修改asm实例中用户密码


       The SYSASM privilege enables the separation of the database operating system credentials from the ASM credentials.
        Use the SYSASM privilege instead of the SYSDBA privilege to connect to and administer an ASM instance.If you use the SYSDBA privilege
        to connect to an ASM instance, then Oracle writes warnings to the alert log file because commands that you run using the SYSDBA privilege
        on an ASM instance will eventually be deprecated.
        Oracle writes alerts to the alert log files if you issue CREATE, ALTER, or DROP DISKGOUP statements that should be performed by SYSASM.
        In 11.2 you can use asmcmd to change the password for single instance environment as follows
        $ export ORACLE_SID=+ASM
        $ asmcmd
        ASMCMD> passwd sys
        Enter old password (optional): ******
        Enter new password: ******
         In Cluster environment
        ASMCMD> orapwusr --modify --password sys
        Enter password: ******
        ASMCMD> exit
         ASM instance sysdba privileged will be deprecated and sysasm will be replaced.
AODU>


AODU> asm general    ---asm磁盘的选择,ASM磁盘的限制及ASM实例SGA设置


       ****ASM disk option****
        http://asmsupportguy.blogspot.com.au/
        1) Raw disk partition.A raw partition can be the entire disk drive or a section of a disk drive. However,the ASM disk
        cannot be in a partition that includes the partition table because the partition table can be overwritten.
        2) Logical unit numbers (LUNs),Using hardware RAID functionality to create LUNs is a recommended approach. Storage hardware
        RAID 0+1 or RAID5, and other RAID configurations, can be provided to ASM as ASM disks.
        3) Raw logical volumes (LVM).LVMs are supported in less complicated configurations where an LVM is mapped to a LUN, or an LVM
        uses disks or raw partitions. LVM configurations are not recommended by Oracle because they create a duplication of functionality.
        Oracle also does not recommended using LVMs for mirroring because ASM already provides mirroring.
        4) NFS NAS files.If you have a certified NAS device, then you can create zero-padded files in an NFS mounted directory and use those files
        as disk devices in an Oracle Automatic Storage Management (Oracle ASM) diskgroup.

        ****The procedures for preparing storage resources for ASM****
        1) Identify or create the storage devices for ASM by identifying all of the storage resource device names that you can use to create an
        ASM disk group.For example,on Linux systems,device names are typically presented from the /dev directory with the
        /dev/device_name_identifier name syntax.
        2) Change the ownership and the permissions on storage device resources. For example, the following steps are required on Linux systems:
                2.1) Change the user and group ownership of devices to oracle:dba or grid:asmadmin (See ASM Role Separation document: 1376731.1 )
                2.2) Change the device permissions to read/write
                2.3) On older Linux versions, you must configure raw device binding
        After you have configured ASM, ensure that disk discovery has been configured correctly by setting the ASM_DISKSTRING initialization parameter.
        Note: Setting the ownership to oracle:dba is just one example that corresponds to the default settings.A non-default installation may require
        different settings. In general, the owner of the disk devices should be the same as the owner of the Oracle binary.
        The group ownership should be OSDBA of the ASM instance, which is defined at installation.

        ****Recommendations for Storage Preparation****
        1) Configure two disk groups, one for the datafile and the other for the Flash Recovery Area.For availability purposes,
        one is used as a backup for the other.
        2) Ensure that LUNs,which are disk drives of partitions,that ASM disk groups use have similar storage performance and availability
        characteristics.In storage configurations with mixed speed drives,such as 10K and 15K RPM,I/O distribution is constrained by the slowest speed drive.
        3) Be aware that ASM data distribution policy is capacity-based.LUNs provided to ASM have the same capacity for each disk group to avoid an imbalance.
        4) Use the storage array hardware RAID 1 mirroring protection when possible to reduce the mirroring overhead on the server.
        Use ASM mirroring redundancy in the absence of a hardware RAID, or when you need host-based volume management functionality,
        such as mirroring across storage systems. You can use ASM mirroring in configurations when mirroring between geographically-separated sites
        over a storage interface.
        Hardware RAID 1 in some lower-cost storage products is inefficient and degrades the performance of the array.ASM redundancy delivers
        improved performance in lower-cost storage products.
        5) Maximize the number of disks in a disk group for maximum data distribution and higher I/O bandwidth.
        6) Create LUNs using the outside half of disk drives for higher performance. If possible, use small disks with the highest RPM.
        7) Create large LUNs to reduce LUN management overhead.
        8) Minimize I/O contention between ASM disks and other applications by dedicating disks to ASM disk groups for those
        disks that are not shared with other applications.
        9) If you are using a high-end storage array that offers robust built-in RAID solutions,then Oracle recommends that you configure redundancy
        in the storage array by enabling RAID protection, such as RAID1 (mirroring) or RAID5 (striping plus parity). For example,
        to create an Oracle ASM disk group where redundancy is provided by the storage array, first create the RAID-protected logical unit
        numbers (LUNs) in the storage array, and then create the Oracle ASM disk group using the EXTERNAL REDUNDANCY clause.
        10) Avoid using a Logical Volume Manager (LVM) because an LVM would be redundant. However, thereare situations where certain multipathing or
        third party cluster solutions require an LVM. In these situations, use the LVM to represent a single LUN without striping or mirroring
        to minimize the performance impact.
        11) For Linux, when possible, use the Oracle ASMLIB feature to address device naming and permission persistency.
        12) ASMLIB provides an alternative interface for the ASM-enabled kernel to discover and access block devices. ASMLIB provides storage and
        operating system vendors the opportunity to supply extended storage-related features. These features provide benefits such as
        improved performance and greater data integrity.

        ****ASM imposes the following limits****
        63 disk groups in a storage system
        10,000 ASM disks in a storage system
        2 terabyte maximum storage for each ASM disk (the Bug 6453944 allowed larger sizes, but that led to problems,
        see Note 736891.1 "ORA-15196 WITH ASM DISKS LARGER THAN 2TB")
        40 exabyte maximum storage for each storage system
        1 million files for each disk group
        2.4 terabyte maximum storage for each file

        Starting on 11.2.0.4 release and onwards ASM MEMORY_MAX_TARGET & MEMORY_TARGET need to be set = 1GB or higher.
        1) ASM MEMORY_TARGET & MEMORY_MAX_TARGET cannot be set less than 1GB on 11.2.0.4 release and onwards.
        2) If ASM MEMORY_TARGET  & MEMORY_MAX_TARGET is set to a lower value < 1GB, then MEMORY_TARGET & MEMORY_MAX_TARGET are automatically
        reset back to its default value MEMORY_TARGET= 1GB
AODU>
AODU> asm sga   ---用于收集ASM实例SGA信息
        ****ASM Instance Shared Pool Settings437924.1****
        The setting for the SHARED_POOL_SIZE parameter determines the amount of memory required to manage the instance.
        The setting for this parameter is also used to determine the amount of space that is allocated for extent storage.
        The default value for this parameter is suitable for most ASM environments.
        Shared Pool in ASM is used for metadata information.
        You do not have to set a value for the SHARED_POOL_SIZE initialization parameter if you use Automatic Memory Management (AMM).
        Oracle strongly recommends that you use Automatic Memory Management (AMM) for ASM. Automatic Memory Management,automatically manages
        the memory-related parameters for ASM instances with the MEMORY_TARGET parameter. AMM is enabled by default on ASM instances,even when
        the MEMORY_TARGET parameter is not explicitly set. The default value used for MEMORY_TARGET (272 MB) is acceptable for most environments.
        This is the only parameter that you need to set for complete ASM memory management. You can also increase MEMORY_TARGET dynamically,
        up to the value of the MEMORY_MAX_TARGET parameter, just as you can for a database instance.

        Note: For Linux environments, automatic memory management will not work if /dev/shm is not available or is sized smaller than MEMORY_TARGET.
        For Enterprise Linux Release 5, /dev/shm is configured to be half the size of the system memory by default.You can adjust this by
        adding a size option to the entry for /dev/shm in /etc/fstab. For more details, see the man page for the mount command.
        Note: The minimum MEMORY_TARGET for ASM is 256 MB in the SPFILE.If you set MEMORY_TARGET to a lower value,
        Oracle Database increases the value to 256 MB automatically.
        If you are not using Automatic Memory Management, then the default value for this parameter is suitable for most environments.
        =)> For 32-bit environments 32 MB is the default and minimum requirement for an ASM instance, but 128 MB is recommended.
        =)> On 64-bit platforms 88 MB are required for an ASM instance,recommended values is 150 MB.

        For ASM release 11.2.0.3/11.2.0.4/12.1 or before upgrade to ASM release 11.2.0.3/11.2.0.4/12.1, please follow the next recommendation:
        Log in to ASM:
        SQL> show parameter memory_target
        If the value is smaller than 1536m, issue the following:
        SQL> alter system set memory_max_target=4096m scope=spfile;
        SQL> alter system set memory_target=1536m scope=spfile;
        The number 1536m has proven to be sufficient for most environment, the change will not be effective until next restart.

        Database Instance Shared Pool Settings for Use with ASM:
        When you do not use Automatic Memory Management in a database instance,the SGA parameter settings for a database instance may require
        minor modifications to support ASM. When you use Automatic Memory Management, the sizing data discussed below can be treated as
        informational only or as supplemental information to help determine the appropriate values that you should use for the SGA.
        Oracle highly recommends using automatic memory management.
        The following are configuration guidelines for Shared Pool sizing on the database instance (when Automatic Memory Management is not used):
        SHARED_POOL_SIZE initialization parameter.Aggregate the values from the following queries to obtain the current database storage size that is
        either on Oracle ASM or stored in Oracle ASM. Next, determine the redundancy type and calculate the SHARED_POOL_SIZE using
        the aggregated value as input.
        SELECT SUM(bytes)/(1024*1024*1024) FROM V$DATAFILE;
        SELECT SUM(bytes)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b WHERE a.group#=b.group#;
        SELECT SUM(bytes)/(1024*1024*1024) FROM V$TEMPFILE WHERE status='ONLINE';
         For disk groups using external redundancy, every 100 GB of space needs 1 MB of extra shared pool plus 2 MB.
         For disk groups using normal redundancy, every 50 GB of space needs 1 MB of extra shared pool plus 4 MB.
         For disk groups using high redundancy, every 33 GB of space needs 1 MB of extra shared pool plus 6 MB.

        ****ASM Shared Pool RAC Considerations:****
        When Migrating from single instance to RAC add an additional 15% more shared pool to the Database & ASM instances, since RAC-specific
        memory is mostly allocated in the shared pool at SGA creation time (that value is heuristic, based on RAC sizing experience).
        In 11.2.0.3/11.2.0.4, the "PROCESSES" parameter will be default to "available CPU cores * 80 + 40" (in the ASM spfile).
        As the default value for "MEMORY_TARGET" is based on "PROCESSES", it can be insufficient if there is a large number of CPU cores or
        large number of diskgroups which could cause issues (i.e. Grid Infrastructure stack fails to stop with ORA-04031 etc)
        per Bug:13605735 & Bug:12885278, it is recommended to increase the value of MEMORY_MAX_TARGET & MEMORY_TARGET before
        upgrading/installing to 11.2.0.3/11.2.0.4 (does not apply to 10g ASM).

        ****ASM Shared Pool****
        spool asm<#>_401_shared_pool.html
        SET MARKUP HTML ON
        set echo on
        set pagesize 200
        alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
        select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;
        select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
        select bytes/1024/1024 MB from v$sgastat where pool = 'shared pool' and name = 'free memory';
        select bytes/1024/1024 MB from v$sgastat where pool = 'large pool' and name = 'free memory';
        select * from V$SHARED_POOL_ADVICE;
        select * from v$version;
        show parameter asm
        show parameter cluster
        show parameter instance_type
        show parameter instance_name
        show sga
        show parameter
        spool off
        exit
AODU>


AODU> asm reblance   ---ASM重新平衡的阶段和操作。当增加、移除磁盘时会发生这样的操作。

        The rebalance operation has three phases:planning,file extents relocation and disk compacting.
        As far as the overall time to complete is concerned, the planing phase time is insignificant so there is no need to worry about it.
        The extent relocation phase will take most of the time, so the main focus will be on that.
        The compact phase is part of rebalance operation,it moves the data as close as possible to the outer tracks of the disks(the lower numbered offsets)
        The first time you run rebalance with 11g,it could take a while if the disk group configuration changed (especially via ADD DISK)
        when running with 10g ASM. Subsequent manual rebalance without a configuration change should not take as much time.
        A disk group where the compact phase of rebalance has done a lot of work will tend to have better performance than the pre-compact disk group.
        The data should be clustered near the higher performing tracks of the disk, resulting less seektime .
         It's enabled by default from 11g onwards.
         It generally takes place at the end of rebalance operation.
        Before 12c, we cannot see compact phase on v$asm_operation view at asm level. If one see EST_MINUTES shows as '0' and waiting for long time,
        probably its is doing compact. This can be confirmed by seeing system state dump from ASM level and mostly we will see no blocking session
        and waits are "kfk:async IO"
        From 12c onwards, we can see compact phase as separate operation .
         Compact phase can be disabled.
        Before 12c, use hidden parameter _disable_rebalance_compact=true at instance level .
        From 12c onwards, _disable_rebalance_compact parameter is no longer available, however Diskgroup attribute _rebalance_compact can be used:
        SQL> ALTER DISKGROUP <dg> SET ATTRIBUTE "_rebalance_compact"="FALSE";
        The rebalance power defaults to the value of the ASM_POWER_LIMIT initialization parameter (default = 1).
        The higher the limit, the faster a rebalance operation may complete. Lower values cause rebalancing to take longer, but consume
        fewer processing and I/O resources. This leaves these resources available for other applications, such as the database.
        The POWER value can be from 0 to 11 where 0 stops rebalancing and 11 is the fastest.
        It is possible to adjust this parameter dynamically, however adjusting ASM_POWER_LIMIT only affects future rebalances.
        It does not affect an in progress rebalance.
        To change the power of an in progress rebalance, a new rebalance command should be issued with the POWER clause.
        ALTER DISKGROUP <disk group name> REBALANCE [POWER n];
        The asm_power_limit can be found in the v$asm_operation view.
        Altering a diskgroup (add, drop, undrop, resize) will trigger an automatic rebalance operation according to ASM_POWER_LIMIT
        initialization parameter if no POWER clause specified in the alter command. If the optional POWER clause is specified,
        ASM will rebalance the diskgroup using the integer value to override the value that the ASM_POWER_LIMIT initialization parameter.
        SQL> show parameter limit
        NAME                                 TYPE        VALUE
        ------------------------------------ ----------- ------
        asm_power_limit                      integer     1
        SQL> alter diskgroup dg1 add disk
          2  '\\.\ORCLDISKD10',
          3  '\\.\ORCLDISKD20',
          4  '\\.\ORCLDISKD30';
        Diskgroup altered.
        One rebalance process started (ARB0)
        SQL> select * from v$asm_operation;
        GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE
        ------------ ----- ---- ---------- ---------- ---------- ---------- ----------
                   1 REBAL WAIT          1          0          0          0
        alter diskgroup dg1 rebalance power 8;
        SQL> select * from v$asm_operation;
        $asmca -silent -configureASM -sysAsmPassword sysPassw0rd -asmsnmpPassword asmsnmpPassw0rd
        -diskString 'ORCL:*' -diskGroupName DATA -disk 'ORCL:*' -redundancy EXTERNAL
        select i.dbname, g.name diskgroup, i.failgroup, d.path, d.total_mb, i.bytes_read, i.bytes_written from v$asm_disk_iostat i,
        v$asm_diskgroup g, v$asm_disk d where i.group_number=g.group_number and i.disk_number=d.disk_number and i.failgroup=d.failgroup
        order by i.dbname, g.name, i.failgroup, d.path;
        SQL> SELECT level, dir, sys, substr(lpad(' ',2*level,' ')||CONCAT('+'||gname,
             SYS_CONNECT_BY_PATH(aname,'/')),1,60) full_path
             FROM ( SELECT g.name gname, a.parent_index pindex, a.name aname,
             a.reference_index rindex, a.ALIAS_DIRECTORY dir, a.SYSTEM_CREATED sys
                              FROM v$asm_alias a, v$asm_diskgroup g
                            WHERE a.group_number = g.group_number)
             START WITH (MOD(pindex, POWER(2, 24))) = 0
             CONNECT BY PRIOR rindex = pindex
             ORDER BY rtrim(ltrim(full_path))desc, level asc;
        The RESTRICTED mode enables you to perform all maintenance tasks on a disk group in the ASM Instance without any external interaction.
        Rebalance operations performed while the diskgroup is in restricted mode eliminate the lock/unlock extent map messaging
        between ASM instances in Oracle RAC environment,thus improving overall rebalance throughput. AODU>
AODU>
AODU>


AODU> asm note    ---常用的关于ASM的文档

        1187723.1 Master Note for Automatic Storage Management
        SRDC - How to Collect Diagnostics Information for ASM Hanging Issues(Doc ID 1678602.1)
        Things to Consider Before Upgrading to 11.2.0.2 Grid Infrastructure/ASM (Doc ID 1312225.1)
        How To Upgrade ASM from 10.2 to 11.1 (single Instance configuration / Non-RAC)? (Doc ID 736121.1)
        11.2 Configuration KIT (ASM 11gR2 Installation & Configuration, Deinstallation,Upgrade,ASM Job Role Separation(Standalone Only).(Doc ID 1092213.1)
        NOTE:1376731.1 - ASM 11gR2 Grid Infrastructure And RDBMS/Database Installation & Configuration Using ASM Job Role Separation (A Complete Guide).
        NOTE:1528148.1 - How To Setup Partitioned Linux Block Devices Using UDEV (Non-ASMLIB) And Assign Them To ASM?
        NOTE:1092213.1 - ASM 11.2 Configuration KIT(ASM 11gR2 Installation & Configuration,Deinstallation,Upgrade, ASM Job Role Separation(Standalone Only).
        NOTE:1594584.1 - White Paper: ASMLIB Installation & Configuration On MultiPath Mapper Devices (Step by Step Demo) On RAC Or Standalone Configurations.
        NOTE:1461321.1 - How to replace ASMlib with udev
        NOTE:1607877.1 - Configuring Persistent Storage in Oracle Linux 5 and Oracle Linux 6 for Single Path
        NOTE:605828.1 - Configuring non-raw multipath devices for Oracle Clusterware 11g (11.1.0, 11.2.0) on RHEL5/OL5
        NOTE:1538626.1 - Configuring non-raw Multipath Devices for Oracle Clusterware 11g (11.1.0, 11.2.0) on RHEL6/OL6
        Exact Steps To Migrate ASM Diskgroups To Another SAN/Disk-Array/DAS/etc Without Downtime. (Doc ID 837308.1)
        NOTE:1346190.1 - KFED.PL for diagnosing - ORA-15063 ORA-15042 ORA-15020 ORA-15033
        NOTE:1345068.1 - Files for Upload When Creating ASM/Storage Service Requests
        Alternative Procedure To Upgrade ASM From Release 10.2 Or 11.1 To Release 11.2.0.# or
        To 12.1.0.1 On Unix/Linux Configurations (Standalone) Using ASM Role separation.(Doc ID 1645523.1)
        How To Gather The OS Logs For Each Specific OS Platform To Troubleshoot Storage (ACFS/ASM/DNFS/DBFS) Issues.(Doc ID 1349613.1)
        How To Gather & Backup ASM/ACFS Metadata In A Formatted Manner version 10.1, 10.2, 11.1, 11.2 and 12.1?(Doc ID 470211.1)
        11gr2,12cr1 Grid Infrastructure And RDBMS/Database Installation & Configuration Using ASM Job Role Separation(Complete Guide).(Doc ID 1376731.1)
        Unable To Start ASM (ORA-00838 ORA-04031) On 11.2.0.3/11.2.0.4 If OS CPUs # > 64.(Doc ID 1416083.1)
        Collecting Data To Diagnose ASM Disk Header Corruption. (Doc ID 870334.1)
        How to Copy ASM datafile From Primary Database to Standby Database on ASM using RMAN (Doc ID 605234.1)
        How to Restore the Database Using AMDU after Diskgroup Corruption (Doc ID 1597581.1)
AODU>




关键词:aodu 

相关文章

aodu(At Oracle Database Utility)之optim
aodu(At Oracle Database Utility)之asm(二)
aodu(At Oracle Database Utility)之asm(一)
aodu(At Oracle Database Utility)之rac(二)
aodu(At Oracle Database Utility)之rac(一)
aodu(At Oracle Database Utility)之ora600
aodu(At Oracle Database Utility)之asmdisk
aodu(At Oracle Database Utility)之unwrap
aodu(At Oracle Database Utility)之rdba
aodu(At Oracle Database Utility)之drux
aodu(At Oracle Database Utility)之time
aodu(At Oracle Database Utility)之odlog

联系我们

韩朝阳



    Phone:186-9589-1286

    E-mail:ohsdba@qq.com

    微  信: ohsdba


电话(186-9589-1286)

QQ(375349564)

微信(ohsdba)


常用链接

Oracle
eDelivery
My Oracle Support

Exadata 12.1 Document

Exadata 12.2 Document

Oracle 11gR2 Document

Oracle 12cR1 Document

Oracle 12cR2 Document

GoldenGate 12c

Oracle Fusion Middleware

Mysql Document

PostgreSQL

Python

github

sourceforge



Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com