ASM - Add and remove asmdiskgroup in 12c

 

• Add and remove asmdiskgroup in 12c using command line

Make raw partition of disk

[root@srv1 ~]# fdisk /dev/sdf

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel with disk identifier 0x20717bf6.

Changes will remain in memory only, until you decide to write them.

After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to

         switch off the mode (command 'c') and change display units to

         sectors (command 'u').

Command (m for help): n

Command action

   e   extended

   p   primary partition (1-4)

p

Partition number (1-4): 1 dsdsdsd                           

First cylinder (1-1566, default 1):

Using default value 1

Last cylinder, +cylinders or +size{K,M,G} (1-1566, default 1566):

Using default value 1566

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[root@srv1 ~]# fdisk -l

Disk /dev/sda: 85.9 GB, 85899345920 bytes

255 heads, 63 sectors/track, 10443 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x00047a8e

   Device Boot      Start         End      Blocks   Id  System

/dev/sda1   *           1          77      614400   83  Linux

Partition 1 does not end on cylinder boundary.

/dev/sda2              77         599     4194304   83  Linux

Partition 2 does not end on cylinder boundary.

/dev/sda3             599       10444    79076352   83  Linux

Disk /dev/sdb: 12.9 GB, 12884901888 bytes

255 heads, 63 sectors/track, 1566 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0xb374f6ac

   Device Boot      Start         End      Blocks   Id  System

/dev/sdb1               1        1566    12578863+  83  Linux

Disk /dev/sdc: 21.5 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0xbf063e34

   Device Boot      Start         End      Blocks   Id  System

/dev/sdc1               1        2610    20964793+  83  Linux

Disk /dev/sdd: 21.5 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x8af70889

   Device Boot      Start         End      Blocks   Id  System

/dev/sdd1               1        2610    20964793+  83  Linux

Disk /dev/sde: 4294 MB, 4294967296 bytes

255 heads, 63 sectors/track, 522 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0xff585e78

   Device Boot      Start         End      Blocks   Id  System

/dev/sde1               1         522     4192933+  83  Linux

Disk /dev/sdf: 12.9 GB, 12884901888 bytes

255 heads, 63 sectors/track, 1566 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x20717bf6

   Device Boot      Start         End      Blocks   Id  System

/dev/sdf1               1        1566    12578863+  83  Linux

• Create ASM disk using raw partition

[root@srv1 ~]# oracleasm createdisk backup /dev/sdf1

Writing disk header: done

Instantiating disk: done

[root@srv1 ~]# oracleasm listdisks

BACKUP

CRS

DATA

FRA

[root@srv1 ~]# su grid

[grid@srv1 root]$ cd

[grid@srv1 ~]$ . oraenv

ORACLE_SID = [+ASM1] ?

The Oracle base remains unchanged with value /u01/app/grid

[grid@srv1 ~]$ 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     12284     7807                0            7807              0             Y  CRS/

MOUNTED  EXTERN  N         512   4096  1048576     20473    17997                0           17997              0             N  DATA/

MOUNTED  EXTERN  N         512   4096  1048576     20473    20142                0           20142              0             N  FRA/

ASMCMD> exit

[grid@srv1 ~]$

SQL> col PATH for a40

SQL> /

PATH                                     HEADER_STATU

---------------------------------------- ------------

/dev/oracleasm/disks/BACKUP              PROVISIONED

/dev/oracleasm/disks/FRA                 MEMBER

/dev/oracleasm/disks/DATA                MEMBER

/dev/oracleasm/disks/CRS                 MEMBER

SQL> sho parameter ASM_DISKSTRING

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

asm_diskstring                       string      /dev/oracleasm/disks

• Add ASM disk group with EXTERNAL redundancy level.

SQL> CREATE DISKGROUP backup EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/BACKUP';

Diskgroup created.

SQL> select PATH,HEADER_STATUS from v$asm_disk;

PATH                                     HEADER_STATU

---------------------------------------- ------------

/dev/oracleasm/disks/FRA                 MEMBER

/dev/oracleasm/disks/DATA                MEMBER

/dev/oracleasm/disks/CRS                 MEMBER

/dev/oracleasm/disks/BACKUP              MEMBER

[grid@srv1 ~]$ 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     12284    12234                0           12234              0             N  BACKUP/

MOUNTED  EXTERN  N         512   4096  1048576     12284     7807                0            7807              0             Y  CRS/

MOUNTED  EXTERN  N         512   4096  1048576     20473    17997                0           17997              0             N  DATA/

MOUNTED  EXTERN  N         512   4096  1048576     20473    20142                0           20142              0             N  FRA/

ASMCMD>

SQL> set lines 200

SQL> col GROUP_NUMBER for a60

SQL> col GROUP_NUMBER for a40

SQL> col COMPATIBILITY for a40

SQL> col DATABASE_COMPATIBILITY for a40

SQL> select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME                           COMPATIBILITY                            DATABASE_COMPATIBILITY

------------ ------------------------------ ---------------------------------------- ----------------------------------------

  ########## BACKUP                         10.1.0.0.0                               10.1.0.0.0

  ########## FRA                            12.1.0.0.0                               10.1.0.0.0

  ########## DATA                           12.1.0.0.0                               10.1.0.0.0

  ########## CRS                            12.1.0.0.0                               10.1.0.0.0

SQL> ALTER DISKGROUP BACKUP SET ATTRIBUTE 'compatible.asm' = '12.1.0.0.0';

Diskgroup altered.

SQL> select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME                           COMPATIBILITY                            DATABASE_COMPATIBILITY

------------ ------------------------------ ---------------------------------------- ----------------------------------------

  ########## BACKUP                         12.1.0.0.0                               10.1.0.0.0

  ########## FRA                            12.1.0.0.0                               10.1.0.0.0

  ########## DATA                           12.1.0.0.0                               10.1.0.0.0

  ########## CRS                            12.1.0.0.0                               10.1.0.0.0

• Drop diskgroup backup

SQL> DROP DISKGROUP BACKUP INCLUDING CONTENTS;

Diskgroup dropped.

SQL> select  name, compatibility, database_compatibility from v$asm_diskgroup;

NAME                           COMPATIBILITY                            DATABASE_COMPATIBILITY

------------------------------ ---------------------------------------- ----------------------------------------

FRA                            12.1.0.0.0                               10.1.0.0.0

DATA                           12.1.0.0.0                               10.1.0.0.0

CRS                            12.1.0.0.0                               10.1.0.0.0

• Delete at OS level

[grid@srv1 ~]$ oracleasm deletedisk BACKUP

Disk "BACKUP" defines an unmarked device

Dropping disk: failed

Unable to delete disk "BACKUP"

[grid@srv1 ~]$ su root

Password:

[root@srv1 grid]# +ASM1

bash: +ASM1: command not found

[root@srv1 grid]# . oraenv

ORACLE_SID = [+ASM1] ?

The Oracle base remains unchanged with value /u01/app/grid

[root@srv1 grid]# oracleasm deletedisk BACKUP

Disk "BACKUP" defines an unmarked device

Dropping disk: done

[root@srv1 grid]# oracleasm listdisks

CRS

DATA

FRA

[root@srv1 grid]#

RMAN : RMAN-08137: WARNING: archived log not deleted needed for standby or upstream capture process archived log file

Scenario Preview : 

Recently we find some error using rman backup as we are unable to delete archivelog. Although backup was successful but archivelog not deleting at there end. Reason was physical standby database was not sync and archivelog not applied at  DR  site.

Even in primary database  rman setting is set to CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

Solution:

We forcefully deleted archivelog so NO error found at the next rman scheduled backup.

delete noprompt force archivelog all;

Error in rman backup log :

 archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_2_seq_1002.666.1064966457 thread=2 sequence=1002

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_2_seq_1003.667.1064979311 thread=2 sequence=1003

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_1_seq_640.668.1064979313 thread=1 sequence=640

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_2_seq_1004.669.1064979315 thread=2 sequence=1004

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_1_seq_641.670.1064979317 thread=1 sequence=641

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_1_seq_642.671.1064979761 thread=1 sequence=642

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_2_seq_1005.672.1064979763 thread=2 sequence=1005

13659,1       97%

RMAN >  crosscheck archivelog all;

RMAN > delete noprompt force archivelog all;

archived log file name=+FRA2/smrprod/archivelog/2_1065_1051537325.dbf RECID=2981 STAMP=1065309321

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1066_1051537325.dbf RECID=2983 STAMP=1065312067

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1067_1051537325.dbf RECID=2984 STAMP=1065324915

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1068_1051537325.dbf RECID=2986 STAMP=1065324921

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1069_1051537325.dbf RECID=2988 STAMP=1065325462

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1070_1051537325.dbf RECID=2990 STAMP=1065332821

Deleted 427 objects

PT - Oracle Compression Basic and advanced table example

 PT - Oracle Compression Basic and advanced

• Query to check table compression 

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES;

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

ADDRESSES         DISABLED

PRODUCT_INFORMATI DISABLED

ON

LOGON             DISABLED

ORDERS            DISABLED

CARD_DETAILS      DISABLED

INVENTORIES       DISABLED

WAREHOUSES        DISABLED

JOBS              DISABLED

CUSTOMERS         DISABLED

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

PRODUCT_DESCRIPTI DISABLED

ONS

ORDER_ITEMS       DISABLED

V                 DISABLED

JOB_HISTORY       DISABLED

EMP               DISABLED

DEPT              DISABLED

ORDERENTRY_METADA DISABLED

TA

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE       DISABLED

CUST_BCOMPRESSED  ENABLED  BASIC

CUST_ACOMPRESSED  ENABLED  ADVANCED

19 rows selected.

SQL>

• Create table with no compression basic and advanced.

No compression : CUST_SOURCE

Basic Compression : CUST_BCOMPRESSED

Advanced Compression : CUST_ACOMPRESSED

SQL> conn soe/soe

Connected.

SQL> CREATE TABLE CUST_SOURCE

  2  ( CUSTOMER_NO NUMBER(6),

  3  FIRST_NAME VARCHAR2(20),

  4  LAST_NAME VARCHAR2(20),

  5  NOTE1 VARCHAR2(100),

  6  NOTE2 VARCHAR2(100)

  7  ) NOLOGGING PCTFREE 0 TABLESPACE SOETBS;

Table created.

SQL> CREATE TABLE CUST_BCOMPRESSED

  2  ( CUSTOMER_NO NUMBER(6),

  3  FIRST_NAME VARCHAR2(20),

  4  LAST_NAME VARCHAR2(20),

  5  NOTE1 VARCHAR2(100),

  6  NOTE2 VARCHAR2(100)

  7  ) ROW STORE COMPRESS BASIC NOLOGGING TABLESPACE SOETBS;

Table created.

SQL> CREATE TABLE CUST_ACOMPRESSED

  2  ( CUSTOMER_NO NUMBER(6),

  3  FIRST_NAME VARCHAR2(20),

  4  LAST_NAME VARCHAR2(20),

  5  NOTE1 VARCHAR2(100),

  6  NOTE2 VARCHAR2(100)

  7  ) ROW STORE COMPRESS ADVANCED NOLOGGING PCTFREE 0 TABLESPACE SOETBS;

Table created.

• Insert value on all tables.

SQL> INSERT INTO CUST_SOURCE

  2  SELECT LEVEL AS CUSTOMER_NO,

  3  DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) FIRST_NAME,

  4  DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) LAST_NAME,

  5  DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE1,

  6  DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE2

  7  FROM DUAL

  8  CONNECT BY LEVEL <= 100000;

100000 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO CUST_BCOMPRESSED SELECT * FROM CUST_SOURCE;

100000 rows created.

SQL> COMMIT;

Commit complete.

• Gather stats on the all tables.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_SOURCE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_BCOMPRESSED');

PL/SQL procedure successfully completed.

SQL>

• Check block size compression on the all tables

SQL> col TABLE_NAME format a17

SQL> SELECT TABLE_NAME, BLOCKS*8 SIZE_KB, COMPRESSION, PCT_FREE

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME IN ('CUST_SOURCE','CUST_BCOMPRESSED','CUST_ACOMPRESSED');

TABLE_NAME           SIZE_KB COMPRESS   PCT_FREE

----------------- ---------- -------- ----------

CUST_ACOMPRESSED             ENABLED           0

CUST_BCOMPRESSED       15056 ENABLED           0

CUST_SOURCE            15056 DISABLED          0

• Truncate and insert value on compressed table using direct path loading hints . As compare to basic compression there is small difference in advanced. check the status below.

SQL> TRUNCATE TABLE CUST_ACOMPRESSED;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO CUST_ACOMPRESSED SELECT * FROM CUST_SOURCE;

100000 rows created.

SQL> COMMIT;

Commit complete.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_ACOMPRESSED');

PL/SQL procedure successfully completed.

SQL> col TABLE_NAME format a17

SQL> SELECT TABLE_NAME, BLOCKS*8 SIZE_KB, COMPRESSION, PCT_FREE

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME IN ('CUST_SOURCE','CUST_BCOMPRESSED','CUST_ACOMPRESSED');

TABLE_NAME           SIZE_KB COMPRESS   PCT_FREE

----------------- ---------- -------- ----------

CUST_ACOMPRESSED        4360 ENABLED           0

CUST_BCOMPRESSED        4360 ENABLED           0

CUST_SOURCE             5984 DISABLED          0

• Create Basic level of compression from no compressed table

SQL> CREATE TABLE CUST_BCOMPRESSED1 ROW STORE COMPRESS BASIC as select * from CUST_SOURCE ;

Table created.

• Change existing table to compression

SQL> ALTER TABLE CUST_SOURCE ROW STORE COMPRESS BASIC;

Table altered.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES where table_name='CUST_SOURCE';

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE       ENABLED  BASIC

• Change existing table from basic to compression

SQL> ALTER TABLE CUST_SOURCE ROW STORE COMPRESS advanced;

Table altered.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES where table_name='CUST_SOURCE';

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE       ENABLED  ADVANCED

SQL>

SQL> ALTER TABLE CUST_SOURCE noCOMPRESS;

Table altered.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES where table_name='CUST_SOURCE';

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE       DISABLED

• Create tablespace with advanced level of compression

SQL> create tablespace adv_comp datafile 'C:\APP\VARUNYADAV\ORADATA\ORCL2\advcomp01.DMP' size 10m DEFAULT ROW STORE COMPRESS ADVANCED;

Tablespace created.

• Determining Which Rows Are Compressed

SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(OWNNAME => 'SOE',TABNAME => 'SALES_HISTORY',SUBOBJNAME => '',ROW_ID => 'AAAKEIEEGBBADBTDEDD'),

1, 'No Compression',

2, 'Advanced Row Compression',

4, 'Hybrid Columnar Compression for Query High',

8, 'Hybrid Columnar Compression for Query Low',

16, 'Hybrid Columnar Compression for Archive High',

32, 'Hybrid Columnar Compression for Archive Low',

4096, 'Basic Table Compression',

'Unknown Compression Type') COMPRESSION_TYPE

FROM DUAL;

• Compression Related Statistics

SQL> SELECT N.NAME, S.VALUE FROM V$MYSTAT S, V$STATNAME N WHERE S.STATISTIC# = N.STATISTIC# AND N.NAME LIKE '%HSC%';

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

HSC OLTP Space Saving                                               1177689

HSC OLTP Compressed Blocks                                              510

HSC IDL Compressed Blocks                                              3379

HSC Compressed Segment Block Changes                                   6691

HSC Heap Segment Block Changes                                        11782

HSC OLTP Non Compressible Blocks                                        508

HSC OLTP positive compression                                          1018

HSC OLTP negative compression                                             0

HSC OLTP recursive compression                                            0

HSC OLTP inline compression                                            1018

HSC OLTP partial compression                                              0

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

HSC OLTP Drop Column                                                      0

HSC OLTP Compression skipped rows                                         0

HSC OLTP compression block checked                                        0

HSC OLTP Compression wide compressed row pieces                           0

15 rows selected.