Oracle Technology Stack: March 2020

Friday, March 13, 2020

Steps to Add ASM disk to diskgroup

1. Check existing diskgroup details and redundancy

[oracle@orahost01 ~]$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@orahost01 ~]$ 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 1817119 499770 0 499770 0 N DATA/
MOUNTED NORMAL N 512 4096 1048576 550160 543956 137540 203208 0 N FRA/
MOUNTED NORMAL N 512 4096 1048576 18435 18256 6145 6055 0 N OCR/

[oracle@orahost01 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 6 04:45:11 2019

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

SQL> select state, name, type from v$asm_diskgroup;

STATE NAME TYPE
----------- ------------------------------ ------
MOUNTED DATA EXTERN
MOUNTED FRA NORMAL
MOUNTED OCR NORMAL

2. Check existing disk details

SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk;
GROUP_NUMBER MOUNT_S STATE REDUNDA NAME PATH
------------ ------- -------- ------- --------- ------------
0 CLOSED NORMAL UNKNOWN /dev/oracleasm/disks/ASMDATA06
1 CACHED NORMAL UNKNOWN DATA05 /dev/oracleasm/disks/ASMDATA05
1 CACHED NORMAL UNKNOWN DATA04 /dev/oracleasm/disks/ASMDATA04
1 CACHED NORMAL UNKNOWN DATA03 /dev/oracleasm/disks/ASMDATA03
1 CACHED NORMAL UNKNOWN DATA02 /dev/oracleasm/disks/ASMDATA02
1 CACHED NORMAL UNKNOWN DATA01 /dev/oracleasm/disks/ASMDATA01
3 CACHED NORMAL UNKNOWN OCR_00 /dev/oracleasm/disks/OCR_01
2 CACHED NORMAL UNKNOWN FRA_01 /dev/oracleasm/disks/FRA_01
2 CACHED NORMAL UNKNOWN FRA_02 /dev/oracleasm/disks/FRA_02

9 rows selected.

3. Add the disk into diskgroup with same naming convention

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASMDATA06' name DATA06;

Diskgroup altered.

4. Check the current size of diskgroup after adding the disk

SQL> select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup
order by 1;

GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB
------------ --------- --------- ------ ---------- ----------
1 DATA MOUNTED EXTERN 2329103 1011748
2 FRA MOUNTED NORMAL 550160 543956
3 OCR MOUNTED NORMAL 18435 18256

5. You can check the status of the process from below sql statement

SQL> select operation, state, sofar, est_work, est_minutes from v$asm_operation where group_number=1;

OPERATION STATE SOFAR EST_WORK EST_MINUTES
----- ---- ---------- ---------- -----------
REBAL RUN 3346 361739 107

SQL> select operation, state,power, actual, sofar, est_work, est_minutes from v$asm_operation where group_number=1;

OPERATION STATE POWER ACTUAL SOFAR EST_WORK EST_MINUTES
----- ----- ---- ---- ------- ------ ---------
REBAL RUN 1 1 3456 361740 115

5. You can speed up the re-balancing process by changing power limit for diskgroup

SQL> Alter diskgroup DATA rebalance power 4;

SQL> select operation, state,power, actual, sofar, est_work, est_minutes from v$asm_operation where group_number=1;

OPERATION STATE POWER ACTUAL SOFAR EST_WORK EST_MINUTES
----- ----- ---- ---- ------- ------ ---------
REBAL RUN 4 4 1200 324740 205

Thursday, March 12, 2020

Solution for ORA-03113: end-of-file on communication channel

Problem

Following Error occur while restart the database after the unclean shutdown

SQL> startup
ORACLE instance started.
Total System Global Area 4943876096 bytes
Fixed Size 2261688 bytes
Variable Size 989859144 bytes
Database Buffers 3942645760 bytes
Redo Buffers 9109504 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 18379
Session ID: 191 Serial number: 3

Solution

Redo log file might become corrupted while the database is open,Need to clear the corrupted redo logs to open the database.

SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 11 18:35:04 2019

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 4943876096 bytes
Fixed Size 2261688 bytes
Variable Size 989859144 bytes
Database Buffers 3942645760 bytes
Redo Buffers 9109504 bytes
Database mounted.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 4943876096 bytes
Fixed Size 2261688 bytes
Variable Size 989859144 bytes
Database Buffers 3942645760 bytes
Redo Buffers 9109504 bytes
Database mounted.
Database opened.