I/O Activity Balance Across Disks in ASM

Balanced Oracle ASM Disk Groups

Check cluster name using below command:

[grid@rac1 ~]$ cemutlo -n
rac-cluster
[grid@rac1 ~]$

• Make sure that all the disks in the same Oracle ASM disk group are the same size. Oracle ASM writes in a round-robin fashion to the Oracle ASM disk group disks; therefore, small Oracle ASM disks will be full faster than larger disks, which results in unbalanced I/O activity across the Oracle ASM disks.

• All disks in the same Oracle ASM disk group should have the same performance characteristics

Use below query to find out ASM disks are blanced or not :-

SELECT dg.group_number "GROUP#",
       dg.name,
       DECODE (total_dg.total_io, 0, 100, 100 * (DECODE (SIGN (1 - df.sum_io / total_dg.total_io), -1, 0, (1 - df.sum_io / total_dg.total_io)))) "IO_BALANCED"
  FROM (SELECT d.group_number group_number,
                 SUM (ABS ((d.reads + d.writes) - tot.avg_io)) sum_io
            FROM v$asm_disk_stat d,
                 (SELECT group_number,
                           SUM (reads) + SUM (writes),
                           DECODE (COUNT (*), 0, 0, (SUM (reads) + SUM (writes)) / COUNT (*)) avg_io
                      FROM v$asm_disk_stat
                     WHERE header_status = 'MEMBER'
                  GROUP BY group_number) tot
           WHERE header_status = 'MEMBER' AND tot.group_number = d.group_number
        GROUP BY d.group_number) df,
       (SELECT group_number,
                 SUM (reads) + SUM (writes) total_io
            FROM v$asm_disk_stat
           WHERE header_status = 'MEMBER'
        GROUP BY group_number) total_dg,
        V$ASM_DISKGROUP dg
 WHERE df.group_number = total_dg.group_number
 AND df.group_number = dg.group_number;

In above figure shows an example of my environment where there are two disk groups in the Oracle ASM instance: DATA1 and DATA2. As you can see, the I/O activity is distributed equally across the Oracle ASM disks in each disk group. The conclusion here is that the disk groups (DATA1  and DATA2) are totally balanced.




For more explanation check below link:

https://community.oracle.com/docs/DOC-995178

Understanding Archive Log Destination

Change the location of archive logs 

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     68
Next log sequence to archive   70
Current log sequence           70
SQL>
SQL>
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                      29.62                     27.51              51
BACKUP PIECE                      31.04                         0               2
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SQL>
SQL> show parameter db_reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 4182M
SQL>show parameter dest

db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 4182M
diagnostic_dest                      string      /u01/app/oracle
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/backup/archive_log1' scope=both;

System altered.

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/u01/app/oracle/backu
                                                 p/archive_log1
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/backup/archive_log1
Oldest online log sequence     68
Next log sequence to archive   70
Current log sequence           70
SQL>


[oracle@localhost archive_log1]$
[oracle@localhost archive_log1]$
[oracle@localhost archive_log1]$ ls -lrt /u01/app/oracle/backup/archive_log1
total 2728
-rw-r----- 1 oracle oinstall 2790400 Feb 20 10:25 1_70_966712547.dbf
[oracle@localhost archive_log1]$
[oracle@localhost archive_log1]$ ls -lrt /u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_20
total 41728
-rw-r----- 1 oracle oinstall 40453120 Feb 20 00:06 o1_mf_1_68_f8p6b7h9_.arc
-rw-r----- 1 oracle oinstall  2271232 Feb 20 09:55 o1_mf_1_69_f8q8soh1_.arc
[oracle@localhost archive_log1]$

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL> alter system set log_archive_dest_2='location=/u01/app/oracle/backup/archive_log2' scope=both;

System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      location=/u01/app/oracle/backup/archive_log2
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL>

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/backup/archive_log2
Oldest online log sequence     70
Next log sequence to archive   72
Current log sequence           72
SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/backup/archive_log2
Oldest online log sequence     71
Next log sequence to archive   73
Current log sequence           73
SQL>
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         73   52428800        512          1 NO  CURRENT                2198499 20-FEB-18   2.8147E+14
         2          1         71   52428800        512          1 YES INACTIVE               2198050 20-FEB-18      2198247 20-FEB-18
         3          1         72   52428800        512          1 YES ACTIVE                 2198247 20-FEB-18      2198499 20-FEB-18

SQL>

[oracle@localhost archive_log2]$  ls -lrt /u01/app/oracle/backup/archive_log1
total 2956
-rw-r----- 1 oracle oinstall 2790400 Feb 20 10:25 1_70_966712547.dbf
-rw-r----- 1 oracle oinstall  167936 Feb 20 10:29 1_71_966712547.dbf
-rw-r----- 1 oracle oinstall   61952 Feb 20 10:38 1_72_966712547.dbf
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -lrt
total 228
-rw-r----- 1 oracle oinstall 167936 Feb 20 10:29 1_71_966712547.dbf
-rw-r----- 1 oracle oinstall  61952 Feb 20 10:38 1_72_966712547.dbf

[oracle@localhost archive_log2]$



Set archive log destination to FRA
-----------------------------------------

SQL> select name,
round(space_limit / 1048576) space_limit_in_mb,
round(space_used / 1048576) space_used_in_mb,
round((space_used / 1048576) / (space_limit / 1048576),2)*100 percent_usage
from v$recovery_file_dest;  

NAME                                               SPACE_LIMIT_IN_MB SPACE_USED_IN_MB PERCENT_USAGE
-------------------------------------------------- ----------------- ---------------- -------------
/u01/app/oracle/fast_recovery_area                              4182             2537            61

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     71
Next log sequence to archive   73
Current log sequence           73
SQL>
SQL> alter system switch logfile;

System altered.

SQL>

SQL> show parameter RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 4182M
SQL>

[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -l /u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_20
total 41728
-rw-r----- 1 oracle oinstall 40453120 Feb 20 00:06 o1_mf_1_68_f8p6b7h9_.arc
-rw-r----- 1 oracle oinstall  2271232 Feb 20 09:55 o1_mf_1_69_f8q8soh1_.arc
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -l /u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_20
total 42048
-rw-r----- 1 oracle oinstall 40453120 Feb 20 00:06 o1_mf_1_68_f8p6b7h9_.arc
-rw-r----- 1 oracle oinstall  2271232 Feb 20 09:55 o1_mf_1_69_f8q8soh1_.arc
-rw-r----- 1 oracle oinstall   325632 Feb 20 10:50 o1_mf_1_73_f8qd1mhl_.arc
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -lrt /u01/app/oracle/backup/archive_log1
total 2956
-rw-r----- 1 oracle oinstall 2790400 Feb 20 10:25 1_70_966712547.dbf
-rw-r----- 1 oracle oinstall  167936 Feb 20 10:29 1_71_966712547.dbf
-rw-r----- 1 oracle oinstall   61952 Feb 20 10:38 1_72_966712547.dbf
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -lrt /u01/app/oracle/backup/archive_log2
total 548
-rw-r----- 1 oracle oinstall 167936 Feb 20 10:29 1_71_966712547.dbf
-rw-r----- 1 oracle oinstall  61952 Feb 20 10:38 1_72_966712547.dbf
-rw-r----- 1 oracle oinstall 325632 Feb 20 10:50 1_73_966712547.dbf
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$

Recover system datafile until sequence

SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1       8196 2139083                  63
         2          4 2139083                  63
         3          4 2139083                  63
         4          4 2139083                  63
         5          4 2139083                  63
         7          4 2139083                  63

6 rows selected.

SQL>


SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1       8196 2139083                  63
         2          4 2139083                  63
         3          4 2139083                  63
         4          4 2139083                  63
         5          4 2139083                  63
         7          4 2139083                  63

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> !
[oracle@localhost DUCAT]$ cd /u01/app/oracle/oradata/
[oracle@localhost oradata]$ ls
ducat  test
[oracle@localhost oradata]$
[oracle@localhost oradata]$ cd ducat/
[oracle@localhost ducat]$ ls
control01.ctl  example01.dbf  fda01.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@localhost ducat]$
[oracle@localhost ducat]$ mv system01.dbf system01.dbf_bkp
[oracle@localhost ducat]$ ls -lrt
total 2570540
-rw-r----- 1 oracle oinstall 173023232 Feb 15 05:16 temp01.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 15 05:17 redo02.log
-rw-r----- 1 oracle oinstall 671096832 Feb 15 05:20 sysaux01.dbf
-rw-r----- 1 oracle oinstall 363077632 Feb 15 05:20 example01.dbf
-rw-r----- 1 oracle oinstall  52436992 Feb 15 05:20 fda01.dbf
-rw-r----- 1 oracle oinstall  32776192 Feb 15 05:20 users01.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 15 05:25 redo03.log
-rw-r----- 1 oracle oinstall 817897472 Feb 15 05:25 system01.dbf_bkp
-rw-r----- 1 oracle oinstall 424681472 Feb 15 05:25 undotbs01.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 15 05:25 redo01.log
-rw-r----- 1 oracle oinstall   9781248 Feb 15 05:26 control01.ctl
[oracle@localhost ducat]$
[oracle@localhost ducat]$ exit
exit

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

System altered.

SQL> /

System altered.

SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1       8196 2139322                  66
         2          4 2139322                  66
         3          4 2139322                  66
         4          4 2139322                  66
         5          4 2139322                  66
         7          4 2139322                  66

6 rows selected.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost DUCAT]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 15 05:27:34 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             654312576 bytes
Database Buffers          381681664 bytes
Redo Buffers                5632000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ducat/system01.dbf'


SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1          0 0                         0
         2          0 2139751                  66
         3          0 2139751                  66
         4          0 2139751                  66
         5          0 2139751                  66
         7          0 2139751                  66

6 rows selected.

SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost DUCAT]$ 

SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1       8192 2139083                  63
         2          0 2139751                  66
         3          0 2139751                  66
         4          0 2139751                  66
         5          0 2139751                  66
         7          0 2139751                  66

6 rows selected.



restore system datafile here





rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 15 05:28:12 2018

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    1043886080 bytes

Fixed Size                     2259840 bytes
Variable Size                654312576 bytes
Database Buffers             381681664 bytes
Redo Buffers                   5632000 bytes

RMAN> restore datafile 1;

Starting restore at 15-FEB-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ducat/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DUCAT/backupset/2018_02_15/o1_mf_nnndf_TAG20180215T052016_f89lst35_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DUCAT/backupset/2018_02_15/o1_mf_nnndf_TAG20180215T052016_f89lst35_.bkp tag=TAG20180215T052016
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 15-FEB-18

RMAN> recover datafile 1 until sequence 66;

Starting recover at 15-FEB-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 63 is already on disk as file /u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_15/o1_mf_1_63_f89m3dbb_.arc
archived log for thread 1 with sequence 64 is already on disk as file /u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_15/o1_mf_1_64_f89m51w3_.arc
archived log for thread 1 with sequence 65 is already on disk as file /u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_15/o1_mf_1_65_f89m55fr_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_15/o1_mf_1_63_f89m3dbb_.arc thread=1 sequence=63
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-FEB-18

RMAN>



SQL>  select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1       8192 2139749                  66
         2          0 2139751                  66
         3          0 2139751                  66
         4          0 2139751                  66
         5          0 2139751                  66
         7          0 2139751                  66

6 rows selected.

SQL> alter database open;

Database altered.


SQL>

Using spfile with unix softlink in oracle

Use softlink  with spfile in oracle

If we use softlink for spfile then orginal spfile can be on different location  and in dbs use softlink point to original file.




[oracle@localhost ~]$
[oracle@localhost ~]$ cd $ORACLE_HOME
[oracle@localhost db_1]$ cd dbs
[oracle@localhost dbs]$ ls -lrt
total 19108
-rw-r--r-- 1 oracle oinstall    2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall      24 Dec 20 19:18 lkDUCAT
-rw-r----- 1 oracle oinstall    1536 Dec 27 21:32 orapwducat
-rw-r----- 1 oracle oinstall      24 Jan  2 20:25 lkTEST
-rw-r----- 1 oracle oinstall    1536 Jan 15 23:26 orapwtest
-rw-r----- 1 oracle oinstall 9748480 Jan 15 23:37 snapcf_test.f
-rw-rw---- 1 oracle oinstall    1544 Jan 19 17:59 hc_test.dat
-rw-r----- 1 oracle oinstall    2560 Jan 19 18:00 spfiletest.ora
-rw-r----- 1 oracle oinstall 9781248 Feb  3 22:41 snapcf_ducat.f
-rw-rw---- 1 oracle oinstall    1544 Feb 12 20:29 hc_ducat.dat
-rw-r----- 1 oracle oinstall    3584 Feb 13 12:06 spfileducat.ora
[oracle@localhost dbs]$
[oracle@localhost dbs]$ mkdir /u01/app/oracle/backup/pfile
[oracle@localhost dbs]$
[oracle@localhost dbs]$ cp * /u01/app/oracle/backup/pfile
[oracle@localhost dbs]$
[oracle@localhost dbs]$ ls -prt /u01/app/oracle/backup/pfile
hc_ducat.dat  hc_test.dat  init.ora  lkDUCAT  lkTEST  orapwducat  orapwtest  snapcf_ducat.f  snapcf_test.f  spfileducat.ora  spfiletest.ora
[oracle@localhost dbs]$
[oracle@localhost dbs]$
[oracle@localhost dbs]$ rm *
[oracle@localhost dbs]$ ls
[oracle@localhost dbs]$
[oracle@localhost dbs]$
[oracle@localhost dbs]$ ln -s /u01/app/oracle/backup/pfile/spfileducat.ora .
[oracle@localhost dbs]$ ls -lrt
total 0
lrwxrwxrwx 1 oracle oinstall 44 Feb 14 19:00 spfileducat.ora -> /u01/app/oracle/backup/pfile/spfileducat.ora
[oracle@localhost dbs]$
[oracle@localhost dbs]$
[oracle@localhost dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 14 19:00:36 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             654312576 bytes
Database Buffers          381681664 bytes
Redo Buffers                5632000 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DUCAT     READ WRITE

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ ls -lrt
total 8
lrwxrwxrwx 1 oracle oinstall   44 Feb 14 19:00 spfileducat.ora -> /u01/app/oracle/backup/pfile/spfileducat.ora
-rw-r----- 1 oracle oinstall   24 Feb 14 19:01 lkDUCAT
-rw-rw---- 1 oracle oinstall 1544 Feb 14 19:01 hc_ducat.dat
[oracle@localhost dbs]$