asmcmd

$ asmcmd help

showversion
showversion --releasepath --softwarepath
showpatches
showclustermode
showclusterstate

lsdg
(v$asm_diskgroup_stat)

lsdg --discovery
(v$asm_diskgroup)
покажет и dismounted группы

lsdg DATA

lsdg DATA -g
(v$asm_diskgroup_stat)
покажет номера инстансов в кластере

lsdg DATA -g --discovery
покажет дисковую группу на всех нодах кластера в том числе и dismounted


dsget

lsdsk

lsdsk --discovery
(v$asm_disk)
покажет все диски

lsdsk -G DATA
lsdsk -G FRA
lsdsk -G REDO

lsdsk -g -G DATA
(gv$asm_disk_stat)
покажет instance_id

lsdsk --member
только члены asm дисковых групп

lsdsk --candidate
свободные диски, кандидаты в группы

lsdsk -k -G DATA

lsdg -p
с path

lsdsk -t
покажет когда созданы и смонтированы и пути

lsdsk --statistics
покажет i/o статистики

lsdsk *02* -p --discovery
(маска в путях диска)

lsod
покажет какие процессы используют диски

lsod -G DATA

iostat
(v$asm_disk_iostat)

iostat -G FRA

iostat -e
покажет еще и read/write error

iostat -e -t
покажет еще и read_time / write_time

iostat -G DATA --region
статистики по регионам cold_reads/writes  hot_read/writes

iostat -G DATA -e --io
теперь i/o не в байтах
(v$asm_disk_iostat)

spget
путь к файлу параметров

lsct DATA
какие клиенты asm используют диски  (версии asm)

lsct DATA -g
тоже, но для всего кластера


pwd
ls
ls -l
ls -l -g
exit


$asmcmp -p
в приглашении будет полный путь

cd +
cd +DATA/TESTDB/DATAFILE
cd +

ls -l -s
space информация в байтах

ls  --permission

du

du  +DATA/TESTDB/ONLINELOG

find  + sysaux*

find +FRA current*

find --type CONTROLFILE + *

lsof
покажет все файлы открытые asm

lsof -G FRA

lsof --dbname testdb
файлы открытые базой testdb

lsof -G FRA -C testdb1
для db_instance

lsattr -G FRA -l -m
атрибуты дисковой группы

lsattr -G FRA -l -m size
lsattr -G FRA -l -m cell (for exadata)

exit


Не интерактивный режим
$asmcmd find + OCR*


Используя sqlplus:
$sqlplus "/as sysasm"

show parameter power

v$asm_estimate
gv$asm_estimate

v$asm_operation



ASMCMD> lsdsk -G sap_data_new -p --discovery

Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State   Path
        6         0  3916017890  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_17-ASM-SAP_DATA34
        6         1  3916017881  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_18-ASM-SAP_DATA35
        6         2  3916017882  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_19-ASM-SAP_DATA36
        6         3  3916017888  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_20-ASM-SAP_DATA37
        6         4  3916017887  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_21-ASM-SAP_DATA38
        6         5  3916017886  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_22-ASM-SAP_DATA39
        6         6  3916017877  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_23-ASM-SAP_DATA40
        6         7  3916017876  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_24-ASM-SAP_DATA41
        6         8  3916017883  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_25-ASM-SAP_DATA42
        6         9  3916017878  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_26-ASM-SAP_DATA43
        6        10  3916017885  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_27-ASM-SAP_DATA44
        6        11  3916017874  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_28-ASM-SAP_DATA45
        6        12  3916017889  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_29-ASM-SAP_DATA46
        6        13  3916017884  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_30-ASM-SAP_DATA47
        6        14  3916017875  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_31-ASM-SAP_DATA48
        6        15  3916017879  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_32-ASM-SAP_DATA49
        6        16  3916017880  CACHED      MEMBER       ONLINE     NORMAL  /dev/oraasm/HP3PARDR-VDI01_33-ASM-SAP_DATA50
ASMCMD>

на всех нодах
SQL> alter diskgroup data dismount force;


ASMCMD> lsdg -g --discovery
Inst_ID  State       Type    Rebal  Sector  Block       AU  Total_MB   Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED     EXTERN  N         512   4096  1048576   1048576   1047023                0         1047023              0             N  ARCHLOGS/
      2  MOUNTED     EXTERN  N         512   4096  1048576   1048576   1047023                0         1047023              0             N  ARCHLOGS/
      1  MOUNTED     EXTERN  N         512   4096  1048576    204800    204677                0          204677              0             N  REDO_B/
      2  MOUNTED     EXTERN  N         512   4096  1048576    204800    204677                0          204677              0             N  REDO_B/
      1  MOUNTED     EXTERN  N         512   4096  1048576    204800    102230                0          102230              0             N  REDO_FLASH/
      2  MOUNTED     EXTERN  N         512   4096  1048576    204800    102230                0          102230              0             N  REDO_FLASH/
      1  MOUNTED     EXTERN  N         512   4096  1048576    204800     73471                0           73471              0             N  RT_REDO/
      2  MOUNTED     EXTERN  N         512   4096  1048576    204800     73471                0           73471              0             N  RT_REDO/
      1  MOUNTED     EXTERN  N         512   4096  1048576  18350080  13859093                0        13859093              0             N  SAP_DATA/
      2  MOUNTED     EXTERN  N         512   4096  1048576  18350080  13859093                0        13859093              0             N  SAP_DATA/
      1  MOUNTED     EXTERN  N         512   4096  1048576  17825792   2836162                0         2836162              0             N  SAP_DATA_B/
      2  MOUNTED     EXTERN  N         512   4096  1048576  17825792   2836162                0         2836162              0             N  SAP_DATA_B/
      1  DISMOUNTED          N           0      0        0         0         0                0               0              0             N  SAP_DATA_NEW/
      2  DISMOUNTED          N           0      0        0         0         0                0               0              0             N  SAP_DATA_NEW/
      1  MOUNTED     EXTERN  N         512   4096  1048576     65536     60909                0           60909              0             Y  SAP_OCR/
      2  MOUNTED     EXTERN  N         512   4096  1048576     65536     60909                0           60909              0             Y  SAP_OCR/
ASMCMD>



SQL> drop diskgroup sap_data_new force including contents;

Diskgroup dropped.

SQL>



ASMCMD> lsdg -g --discovery
Inst_ID  State    Type    Rebal  Sector  Block       AU  Total_MB   Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      2  MOUNTED  EXTERN  N         512   4096  1048576   1048576   1047023                0         1047023              0             N  ARCHLOGS/
      1  MOUNTED  EXTERN  N         512   4096  1048576   1048576   1047023                0         1047023              0             N  ARCHLOGS/
      2  MOUNTED  EXTERN  N         512   4096  1048576    204800    204677                0          204677              0             N  REDO_B/
      1  MOUNTED  EXTERN  N         512   4096  1048576    204800    204677                0          204677              0             N  REDO_B/
      2  MOUNTED  EXTERN  N         512   4096  1048576    204800    102230                0          102230              0             N  REDO_FLASH/
      1  MOUNTED  EXTERN  N         512   4096  1048576    204800    102230                0          102230              0             N  REDO_FLASH/
      2  MOUNTED  EXTERN  N         512   4096  1048576    204800     73471                0           73471              0             N  RT_REDO/
      1  MOUNTED  EXTERN  N         512   4096  1048576    204800     73471                0           73471              0             N  RT_REDO/
      2  MOUNTED  EXTERN  N         512   4096  1048576  18350080  13859093                0        13859093              0             N  SAP_DATA/
      1  MOUNTED  EXTERN  N         512   4096  1048576  18350080  13859093                0        13859093              0             N  SAP_DATA/
      2  MOUNTED  EXTERN  N         512   4096  1048576  17825792   2836162                0         2836162              0             N  SAP_DATA_B/
      1  MOUNTED  EXTERN  N         512   4096  1048576  17825792   2836162                0         2836162              0             N  SAP_DATA_B/
      2  MOUNTED  EXTERN  N         512   4096  1048576     65536     60909                0           60909              0             Y  SAP_OCR/
      1  MOUNTED  EXTERN  N         512   4096  1048576     65536     60909                0           60909              0             Y  SAP_OCR/
ASMCMD>

Миграция на ASM:

$export ORACLE_SID=SID
$sqlplus / as sysdba

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/SID/control1/control01.ctl
/app/oracle/oradata/SID/control2/control02.ctl


SQL> alter system set control_files='+DATA','+DATA' scope=spfile;
SQL> alter system set db_create_file_dest='+DATA' scope=spfile;

SQL> shutdown immediate;
SQL> startup nomount;
SQL> exit

$rman target /

RMAN> restore controlfile from '/app/oracle/oradata/SID/control1/control01.ctl';

RMAN> alter database mount;

RMAN> backup as copy database format '+DATA';

RMAN> switch database to copy;

RMAN> recover database;

RMAN> exit


$export ORACLE_SID=SID
$sqlplus / as sysdba

SQL> alter database open;

SQL> CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE SIZE 61865984 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP1";

SQL> shutdown immediate;
SQL> startup;

SQL> DROP TABLESPACE TEMP;
SQL> CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE SIZE 61865984 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

SQL> shutdown immediate;
SQL> startup

SQL> DROP TABLESPACE TEMP1;

Или просто добавить временный файл в существующее табличное пространство:

SQL> ALTER TABLESPACE "TEMP" ADD TEMPFILE SIZE 500M AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.
SQL>


SQL>SQL> alter system set db_create_online_log_dest_1='+REDO' scope=spfile;

SQL> shutdown immediate;
SQL> startup;

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/app/oracle/oradata/SID/redoa/redo06a.log
/app/oracle/oradata/SID/redoa/redo05a.log
/app/oracle/oradata/SID/redoa/redo04a.log
/app/oracle/oradata/SID/redoa/redo03a.log
/app/oracle/oradata/SID/redoa/redo02a.log
/app/oracle/oradata/SID/redoa/redo01a.log

6 rows selected.

SQL> alter database drop logfile group 1;
SQL> alter database add logfile group 1 size 52428800;

SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 size 52428800;

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance SID (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/SID/redoa/redo03a.log'


SQL> alter database drop logfile group 4;
SQL> alter database add logfile group 4 size 52428800;

SQL> alter database drop logfile group 5;
SQL> alter database add logfile group 5 size 52428800;

SQL> alter database drop logfile group 6;
SQL> alter database add logfile group 6 size 52428800;

SQL> alter system switch logfile;
SQL> alter system checkpoint;

SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 size 52428800;

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/SID/datafile/system.270.789045335
+DATA/SID/datafile/undotbs1.269.789045319
+DATA/SID/datafile/sysaux.268.789045295
+DATA/SID/datafile/users.271.789045349

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/SID/tempfile/temp.275.789045723

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/SID/controlfile/current.266.789045265
+DATA/SID/controlfile/current.267.789045265

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+REDO/SID/onlinelog/group_1.274.789046953
+REDO/SID/onlinelog/group_2.276.789046981
+REDO/SID/onlinelog/group_4.277.789047043
+REDO/SID/onlinelog/group_5.278.789047061
+REDO/SID/onlinelog/group_3.279.789047111
+REDO/SID/onlinelog/group_6.280.789047303

6 rows selected.

SQL>

Перемещение SPFILE в ASM

[oragrid]$
[oragrid]$ export ORACLE_SID=+ASM
[oragrid]$ asmcmd
ASMCMD> cd +
ASMCMD> ls -l
State Type Rebal Name
MOUNTED EXTERN N DATA/
MOUNTED EXTERN N FRA/
MOUNTED EXTERN N REDOA/
MOUNTED EXTERN N REDOB/
ASMCMD> find --type parameterfile . *
ASMCMD>

[oradb]$
[oradb]$ ls -l /tmp/inittestasm.ora
-rw-r--r-- 1 oradb oinstall 2788 Oct 26 12:31 /tmp/inittestasm.ora
[oradb]$

[oradb]$ export ORACLE_SID=testasm
[oradb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 26 14:11:56 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL>
SQL> startup pfile='/tmp/inittestasm.ora';
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 2085744 bytes
Variable Size 176163984 bytes
Database Buffers 411041792 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL>


SQL> create spfile='+DATA' from pfile='/tmp/inittestasm.ora';

File created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oradb@x4450-p2 tmp]$

ASMCMD>
ASMCMD> find --type parameterfile . *
+DATA/TESTASM_P/PARAMETERFILE/spfile.276.733413321
ASMCMD>

ASMCMD>
ASMCMD> cd +DATA/TESTASM/
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y TEMPFILE/
ASMCMD> mkalias +DATA/TESTASM_P/PARAMETERFILE/spfile.276.733413321 spfiletestasm.ora
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y TEMPFILE/
N spfiletestasm.ora => +DATA/TESTASM_P/PARAMETERFILE/spfile.276.733413321
ASMCMD>


[oradb]$
[oradb]$ more /u01/app/oradb/product/10.2.0/db_1/dbs/inittestasm.ora
SPFILE='+DATA/testasm/spfiletestasm.ora'
[oradb]$

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 2085744 bytes
Variable Size 176163984 bytes
Database Buffers 411041792 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL>
SQL>

ASMCMD

$
$ export ORACLE_SID=+ASM
$ asmcmd
ASMCMD> cd +
ASMCMD> find --type parameterfile . *
+DATA/TESTASM/PARAMETERFILE/spfile.265.730811271
+DATA/TESTASM/spfiletestasm.ora
+DATA/TESTASM_P/PARAMETERFILE/spfile.273.730985901
+DATA/TESTASM_P/PARAMETERFILE/spfile.274.730984881
+DATA/TESTASM_P/PARAMETERFILE/spfile.275.730980865
+DATA/TESTASM_P/PARAMETERFILE/spfile.276.730979619
ASMCMD>
ASMCMD>
ASMCMD> ls -l +DATA/TESTASM/
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y PARAMETERFILE/
Y TEMPFILE/
N spfiletestasm.ora => +DATA/TESTASM_P/PARAMETERFILE/spfile.273.730985901
ASMCMD>
ASMCMD> find --type controlfile . *
+DATA/TESTASM/CONTROLFILE/Current.256.730810345
+FRA/TESTASM/CONTROLFILE/Current.256.730810345
ASMCMD>
ASMCMD> find --type onlinelog . *
+DATA/TESTASM_P/ONLINELOG/group_1.272.733316267
+DATA/TESTASM_P/ONLINELOG/group_2.271.733316269
+DATA/TESTASM_P/ONLINELOG/group_3.270.733316275
+FRA/TESTASM_P/ONLINELOG/group_1.312.733316269
+FRA/TESTASM_P/ONLINELOG/group_2.549.733316273
+FRA/TESTASM_P/ONLINELOG/group_3.310.733316277
+REDOA/TESTASM/ONLINELOG/group_4.257.730910813
+REDOA/TESTASM/ONLINELOG/group_5.256.730910819
+REDOA/TESTASM/ONLINELOG/group_6.261.730910825
+REDOB/TESTASM/ONLINELOG/group_4.257.730910815
+REDOB/TESTASM/ONLINELOG/group_5.256.730910821
+REDOB/TESTASM/ONLINELOG/group_6.261.730910825
ASMCMD>
ASMCMD> find --type datafile . *
+DATA/TESTASM/DATAFILE/SYSAUX.262.730810363
+DATA/TESTASM/DATAFILE/SYSTEM.260.730810349
+DATA/TESTASM/DATAFILE/UNDOTBS1.261.730810359
+DATA/TESTASM/DATAFILE/USERS.264.730810367
ASMCMD>
ASMCMD>

ASM Duplicate Controlfiles

$ export ORACLE_SID=testasm
$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Sep 16 15:58:23 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/testasm/controlfile/current.256.729705169

SQL>



$ export ORACLE_SID=+ASM
$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 254867 253505 0 253505 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 284667 284496 0 284496 0 N FRA/
MOUNTED EXTERN N 512 4096 1048576 30719 30354 0 30354 0 N REDO/
ASMCMD> cd +
ASMCMD> pwd
+
ASMCMD> find --type controlfile . *
+DATA/TESTASM/CONTROLFILE/Current.256.729705169
ASMCMD> exit
$


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 897581056 bytes
Fixed Size 2088056 bytes
Variable Size 230687624 bytes
Database Buffers 658505728 bytes
Redo Buffers 6299648 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


$ rman nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Sep 16 16:13:29 2010

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

RMAN> connect target;

connected to target database: testasm (not mounted)
using target database control file instead of recovery catalog

RMAN> restore controlfile to '+FRA' from '+DATA/testasm/controlfile/current.256.729705169';

Starting restore at 16-SEP-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 16-SEP-10

RMAN> restore controlfile to '+REDO' from '+DATA/testasm/controlfile/current.256.729705169';

Starting restore at 16-SEP-10
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 16-SEP-10

RMAN> exit


Recovery Manager complete.
$


$ asmcmd
ASMCMD> find --type controlfile . *
+DATA/TESTASM/CONTROLFILE/Current.256.729705169
+FRA/TESTASM/CONTROLFILE/current.261.729880549
+REDO/TESTASM/CONTROLFILE/current.260.729880559
ASMCMD> exit
$


$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Sep 16 16:36:17 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set control_files=
2 '+DATA/TESTASM/CONTROLFILE/Current.256.729705169',
3 '+FRA/TESTASM/CONTROLFILE/current.261.729880549',
4 '+REDO/TESTASM/CONTROLFILE/current.260.729880559'
5 scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 897581056 bytes
Fixed Size 2088056 bytes
Variable Size 230687624 bytes
Database Buffers 658505728 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/testasm/controlfile/current.256.729705169
+FRA/testasm/controlfile/current.261.729880549
+REDO/testasm/controlfile/current.260.729880559

SQL>