RMAN Status Query!!!

RMAN Status Query!!!

How to get RMAN information about the backup type, status, start time,end time, Size of the backup etc!!!

How to find the backup size?

How to check the RMAN backup status?

How to check the previous RMAN backup status?

#Calculate the RMAN backup size 

SET PAGES 2000 LINES 200

SELECT TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD') COMPLETION_TIME, TYPE, ROUND(SUM(BYTES)/1048576) MB, ROUND(SUM(ELAPSED_SECONDS)/60) MIN 

FROM 

(SELECT 

CASE 

WHEN S.BACKUP_TYPE='L' THEN 'ARCHIVELOG' 

WHEN S.CONTROLFILE_INCLUDED='YES' THEN 'CONTROLFILE' 

WHEN S.BACKUP_TYPE='D' AND S.INCREMENTAL_LEVEL=0 THEN 'LEVEL0' 

WHEN S.BACKUP_TYPE='I' AND S.INCREMENTAL_LEVEL=1 THEN 'LEVEL1' 

END TYPE, 

TRUNC(S.COMPLETION_TIME) COMPLETION_TIME, P.BYTES, S.ELAPSED_SECONDS 

FROM V$BACKUP_PIECE P, V$BACKUP_SET S 

WHERE P.STATUS='A' AND P.RECID=S.RECID 

UNION ALL 

SELECT 'DATAFILECOPY' type, TRUNC(COMPLETION_TIME), OUTPUT_BYTES, 0 ELAPSED_SECONDS FROM V$BACKUP_COPY_DETAILS) 

GROUP BY TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD'), TYPE 

ORDER BY 1 ASC,2,3; 

RMAN> LIST BACKUP OF DATABASE SUMMARY;

RMAN> LIST BACKUP TAG LV0BKP;

#Calculate RMAN backup status, size, timing and session key
#RMAN information about the backup type, status, start time,end time, Size of the backup etc.

SET PAGES 2000 LINES 200

COL STATUS FORMAT a9

COL hrs FORMAT 999.99

select SESSION_KEY,

INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'mm/dd/yy HH24:MI:SS') START_TIME,

TO_CHAR(END_TIME,'mm/dd/yy HH24:MI:SS') END_TIME,

ELAPSED_SECONDS/3600 HRS,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;

#The following query shows the backup job speed ordered by session key, which the primary key for the RMAN session. The columns in_sec and out_sec columns display the data input and output per second.

SET PAGES 2000 LINES 200

COL OPTIMIZED for a10

COL INPUT_PER_SEC FORMAT a20

COL OUTPUT_PER_SEC FORMAT a20

COL TIME_TAKEN_DISPLAY FORMAT a10

SELECT SESSION_KEY,

INPUT_TYPE,

OPTIMIZED,

COMPRESSION_RATIO,

INPUT_BYTES_PER_SEC_DISPLAY INPUT_PER_SEC,

OUTPUT_BYTES_PER_SEC_DISPLAY OUTPUT_PER_SEC,

TIME_TAKEN_DISPLAY

FROM V$RMAN_BACKUP_JOB_DETAILS

ORDER BY SESSION_KEY;

#RMAN information about the backup type, status, start time,end time, Size of the backup etc from Recovery Catalog

SET PAGES 2000 LINES 200

COL STATUS FORMAT a9

COL HRS FORMAT 999.99

SELECT DB_NAME,

INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'MM/DD/YY HH24:MI:SS') START_TIME,

TO_CHAR(END_TIME,'MM/DD/YY HH24:MI:SS') END_TIME,

ELAPSED_SECONDS/3600 HRS,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM RC_RMAN_BACKUP_JOB_DETAILS

–WHERE DB_NAME='SBLTPS'

ORDER BY DB_NAME,SESSION_KEY;

Reference

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmreprt.htm#CHDBIGHG

Log:

====

[oracle@node1 ~]$ . oraenv

ORACLE_SID = [oracle] ? ORA19C

The Oracle base has been set to /u01/app/oracle

[oracle@node1 ~]$

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 15 14:54:33 2023

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> SET PAGES 2000 LINES 200

SELECT TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD') COMPLETION_TIME, TYPE, ROUND(SUM(BYTES)/1048576) MB, ROUND(SUM(ELAPSED_SECONDS)/60) MIN

FROM

(SELECT

CASE

WHEN S.BACKUP_TYPE='L' THEN 'ARCHIVELOG'

WHEN S.CONTROLFILE_INCLUDED='YES' THEN 'CONTROLFILE'

WHEN S.BACKUP_TYPE='D' AND S.INCREMENTAL_LEVEL=0 THEN 'LEVEL0'

WHEN S.BACKUP_TYPE='I' AND S.INCREMENTAL_LEVEL=1 THEN 'LEVEL1'

SQL>   2    3    4    5    6    7    8    9  END TYPE,

TRUNC(S.COMPLETION_TIME) COMPLETION_TIME, P.BYTES, S.ELAPSED_SECONDS

FROM V$BACKUP_PIECE P, V$BACKUP_SET S

 10   11   12  WHERE P.STATUS='A' AND P.RECID=S.RECID

UNION ALL

 13   14  SELECT 'DATAFILECOPY' type, TRUNC(COMPLETION_TIME), OUTPUT_BYTES, 0 ELAPSED_SECONDS FROM V$BACKUP_COPY_DETAILS)

 15  GROUP BY TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD'), TYPE

 16  ORDER BY 1 ASC,2,3;

COMPLETION_TIME      TYPE                 MB        MIN

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

2023-AUG-01          DATAFILECOPY        776          0

2023-SEP-12          ARCHIVELOG           33          0

2023-SEP-13          ARCHIVELOG          250          2

2023-SEP-14          ARCHIVELOG          225          2

2023-SEP-14          CONTROLFILE         565          1

2023-SEP-14          DATAFILECOPY       3795          0

2023-SEP-15          ARCHIVELOG          183          1

2023-SEP-15          CONTROLFILE         657          1

8 rows selected.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 15 14:55:15 2023

Version 19.3.0.0.0

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

connected to target database: ORA19C (DBID=1121712241)

RMAN> LIST BACKUP OF DATABASE SUMMARY;

using target database control file instead of recovery catalog

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

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

42981   B  1  A DISK        14-SEP-23       1       1       NO         MALLIK_BACKUP_TAG

RMAN> LIST BACKUP TAG MALLIK_BACKUP_TAG;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

42981   Incr 1  253.69M    DISK        00:00:08     14-SEP-23

        BP Key: 42991   Status: AVAILABLE  Compressed: NO  Tag: MALLIK_BACKUP_TAG

        Piece Name: /var/rubrik/oracle/f9707ae9-ff5d-4333-8291-2b234a6a4865_backup/c0/fh26bpf3_1_1

  List of Datafiles in backup set 42981

  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name

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

  1    1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_system_j64p3dcg_.dbf

  3    1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_sysaux_j64p4hpv_.dbf

  4    1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_undotbs1_j64p58v3_.dbf

  7    1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_users_j64p59w2_.dbf

  14   1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_user1_j8r9y84m_.dbf

  16   1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_mallik2_j909fm6p_.dbf

  20   1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_test1_ktlw1tvs_.dbf

RMAN> exit

Recovery Manager complete.

[oracle@node1 ~]$

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 15 14:55:41 2023

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> SET PAGES 2000 LINES 200

SQL> COL STATUS FORMAT a9

COL hrs FORMAT 999.99

select SESSION_KEY,

INPUT_TYPE,

STATUS,

SQL> SQL>   2    3    4  TO_CHAR(START_TIME,'mm/dd/yy HH24:MI:SS') START_TIME,

  5  TO_CHAR(END_TIME,'mm/dd/yy HH24:MI:SS') END_TIME,

ELAPSED_SECONDS/3600 HRS,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

  6  OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;  7    8    9   10   11

SESSION_KEY INPUT_TYPE    STATUS    START_TIME        END_TIME              HRS SUM_BYTES_BACKED_IN_GB SUM_BACKUP_PIECES_IN_GB OUTPUT_DEVICE_TYP

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

<Truncated>

     215289 DB INCR       COMPLETED 09/14/23 13:43:46 09/14/23 13:45:05     .02             2.54919434              .333282471 DISK

     215305 DB INCR       COMPLETED 09/14/23 13:57:55 09/14/23 13:59:45     .03             2.74450684              .528594971 DISK

     215326 ARCHIVELOG    COMPLETED 09/14/23 14:08:08 09/14/23 14:08:38     .01             .046971798               .04708147 DISK

     215336 ARCHIVELOG    COMPLETED 09/14/23 14:11:57 09/14/23 14:12:11     .00             .042680264              .042789936 DISK

     215346 ARCHIVELOG    COMPLETED 09/14/23 15:08:08 09/14/23 15:08:31     .01             .047270775              .047380447 DISK

     215356 ARCHIVELOG    COMPLETED 09/14/23 16:08:28 09/14/23 16:08:42     .00             .047048092              .047157764 DISK

     215366 ARCHIVELOG    COMPLETED 09/14/23 17:08:18 09/14/23 17:08:32     .00              .04712677              .047236443 DISK

     215376 ARCHIVELOG    COMPLETED 09/14/23 18:08:44 09/14/23 18:08:59     .00             .047767639              .047877312 DISK

     215386 ARCHIVELOG    COMPLETED 09/14/23 19:08:32 09/14/23 19:08:46     .00              .04718399              .047293663 DISK

     215396 ARCHIVELOG    COMPLETED 09/14/23 20:08:36 09/14/23 20:08:58     .01             .046964645              .047074318 DISK

     215406 ARCHIVELOG    COMPLETED 09/14/23 21:08:48 09/14/23 21:09:03     .00             .047271729              .047381401 DISK

     215416 ARCHIVELOG    COMPLETED 09/14/23 22:09:00 09/14/23 22:09:14     .00              .12175703              .121866703 DISK

     215426 ARCHIVELOG    COMPLETED 09/14/23 23:09:00 09/14/23 23:09:15     .00             .084067345              .084177017 DISK

     215436 ARCHIVELOG    COMPLETED 09/15/23 00:09:46 09/15/23 00:10:17     .01             .047124386              .047234058 DISK

     215446 ARCHIVELOG    COMPLETED 09/15/23 01:09:47 09/15/23 01:10:18     .01             .047052383              .047162056 DISK

     215456 ARCHIVELOG    COMPLETED 09/15/23 02:09:43 09/15/23 02:10:06     .01             .046983242              .047092915 DISK

     215466 ARCHIVELOG    COMPLETED 09/15/23 03:09:44 09/15/23 03:09:59     .00             .047057629              .047167301 DISK

     215476 ARCHIVELOG    COMPLETED 09/15/23 04:09:39 09/15/23 04:09:54     .00             .047075272              .047184944 DISK

     215486 ARCHIVELOG    COMPLETED 09/15/23 05:09:31 09/15/23 05:09:42     .00             .046983242              .047092915 DISK

     215496 ARCHIVELOG    COMPLETED 09/15/23 06:09:47 09/15/23 06:10:02     .00             .048101902              .048211575 DISK

     215506 ARCHIVELOG    COMPLETED 09/15/23 07:09:49 09/15/23 07:10:00     .00             .046898365              .047008038 DISK

     215516 ARCHIVELOG    COMPLETED 09/15/23 08:09:57 09/15/23 08:10:11     .00             .046968937              .047078609 DISK

     215526 ARCHIVELOG    COMPLETED 09/15/23 09:09:39 09/15/23 09:09:45     .00             .047005177              .047114849 DISK

     215536 ARCHIVELOG    COMPLETED 09/15/23 10:09:45 09/15/23 10:09:51     .00             .046883583              .046993256 DISK

     215546 ARCHIVELOG    COMPLETED 09/15/23 11:09:45 09/15/23 11:09:52     .00             .046927452              .047037125 DISK

     215556 ARCHIVELOG    COMPLETED 09/15/23 12:10:14 09/15/23 12:10:37     .01             .047740459              .047850132 DISK

     215566 ARCHIVELOG    COMPLETED 09/15/23 13:10:16 09/15/23 13:10:31     .00             .046950817               .04706049 DISK

     215576 ARCHIVELOG    COMPLETED 09/15/23 14:10:21 09/15/23 14:10:32     .00              .04672718              .046836853 DISK

1209 rows selected.

SQL>

SQL> SET PAGES 2000 LINES 200

COL OPTIMIZED for a10

COL INPUT_PER_SEC FORMAT a20

COL OUTPUT_PER_SEC FORMAT a20

COL TIME_TAKEN_DISPLAY FORMAT a10

SELECT SESSION_KEY,

INPUT_TYPE,

OPTIMIZED,

COMPRESSION_RATIO,

INPUT_BYTES_PER_SEC_DISPLAY INPUT_PER_SEC,

OUTPUT_BYTES_PER_SEC_DISPLAY OUTPUT_PER_SEC,

TIME_TAKEN_DISPLAY

FROM V$RMAN_BACKUP_JOB_DETAILS

ORDER BY SESSION_KEY;SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9

SESSION_KEY INPUT_TYPE    OPTIMIZED  COMPRESSION_RATIO INPUT_PER_SEC        OUTPUT_PER_SEC       TIME_TAKEN

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

<Truncated>

     215289 DB INCR       NO                7.64875011    33.04M                4.32M            00:01:19

     215305 DB INCR       NO                5.19207898    25.55M                4.92M            00:01:50

     215326 ARCHIVELOG    NO                         1     1.60M                1.61M            00:00:30

     215336 ARCHIVELOG    NO                         1     3.12M                3.13M            00:00:14

     215346 ARCHIVELOG    NO                         1     2.10M                2.11M            00:00:23

     215356 ARCHIVELOG    NO                         1     3.44M                3.45M            00:00:14

     215366 ARCHIVELOG    NO                         1     3.45M                3.46M            00:00:14

     215376 ARCHIVELOG    NO                         1     3.26M                3.27M            00:00:15

     215386 ARCHIVELOG    NO                         1     3.45M                3.46M            00:00:14

     215396 ARCHIVELOG    NO                         1     2.19M                2.19M            00:00:22

     215406 ARCHIVELOG    NO                         1     3.23M                3.23M            00:00:15

     215416 ARCHIVELOG    NO                         1     8.91M                8.91M            00:00:14

     215426 ARCHIVELOG    NO                         1     5.74M                5.75M            00:00:15

     215436 ARCHIVELOG    NO                         1     1.56M                1.56M            00:00:31

     215446 ARCHIVELOG    NO                         1     1.55M                1.56M            00:00:31

     215456 ARCHIVELOG    NO                         1     2.09M                2.10M            00:00:23

     215466 ARCHIVELOG    NO                         1     3.21M                3.22M            00:00:15

     215476 ARCHIVELOG    NO                         1     3.21M                3.22M            00:00:15

     215486 ARCHIVELOG    NO                         1     4.37M                4.38M            00:00:11

     215496 ARCHIVELOG    NO                         1     3.28M                3.29M            00:00:15

     215506 ARCHIVELOG    NO                         1     4.37M                4.38M            00:00:11

     215516 ARCHIVELOG    NO                         1     3.44M                3.44M            00:00:14

     215526 ARCHIVELOG    NO                         1     8.02M                8.04M            00:00:06

     215536 ARCHIVELOG    NO                         1     8.00M                8.02M            00:00:06

     215546 ARCHIVELOG    NO                         1     6.86M                6.88M            00:00:07

     215556 ARCHIVELOG    NO                         1     2.13M                2.13M            00:00:23

     215566 ARCHIVELOG    NO                         1     3.21M                3.21M            00:00:15

     215576 ARCHIVELOG    NO                         1     4.35M                4.36M            00:00:11

1209 rows selected.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@node1 ~]$

Regards,

Mallik

ACFS Administration (Create, Increase, Delete)

ACFS Administration (Create, Increase, Delete)

For the pre-requisite please refer the below blog post:

https://mallik034.blogspot.com/2023/08/acfs-9459-advmacfs-is-not-supported-on.html

1. Verify the ACFS Modules Installed:

lsmod |grep ora

2. Verify the diskgroup ACFS_DG mount on all the RAC cluster nodes

crsctl stat res ora.ACFS_DG.dg -t

3. Create 10G ACFS volume acfs_vol out of 40G ACFS_DG diskgroup

asmcmd -p lsdg ACFS_DG

asmcmd volcreate -G ACFS_DG -s 1G acfs_vol

4. Verify the ACFS volume information 

asmcmd volinfo -G ACFS_DG acfs_vol

5. Check acfs volume services at cluster side

crsctl stat res -t | grep -i "advm"

crsctl stat res ora.ACFS_DG.ACFS_VOL.advm -t

6. Create ACFS file system for ACFS devices created by ACFS Volume

mkfs -t acfs /dev/asm/acfs_vol-160

mkdir -p /acfs_test

chown oracle:oinstall /acfs_test_mount_point

7. Register the ACFS device to oracle as a owner 

/sbin/acfsutil registry -a  /dev/asm/acfs_vol-160 /acfs_test_mount_point -u oracle

crsctl stat res -t | grep -i "acfs_dg"

crsctl stat res ora.acfs_dg.acfs_vol.acfs -t

8. Use srvctl command to mount and unmount ACFS filesystem

srvctl status filesystem -d /dev/asm/acfs_vol-160

srvctl stop filesystem -d /dev/asm/acfs_vol-160

srvctl start filesystem -d /dev/asm/acfs_vol-160

df -h /acfs_test_mount_point

9. Verify the ACFS Volume information again which is mounted on OS filesystem

asmcmd volinfo -G ACFS_DG acfs_vol

10. Verify the configuration of ACFS filesystem

srvctl config filesystem

11. Increase the ACFS Volume and ACFS filesystem by 1G which will make total 11G 

acfsutil size +1G –d /dev/asm/acfs_vol-160 /acfs_test_mount_point

asmcmd volinfo -G ACFS_DG acfs_vol

asmcmd -p lsdg ACFS_DG

df -h /acfs_test_mount_point

12. Delete ACFS filesystem and ACFS volume created in case if you are not needed anymore

srvctl stop filesystem -d /dev/asm/acfs_vol-160

/sbin/acfsutil rmfs /dev/asm/acfs_vol-160

asmcmd voldisable -G ACFS_DG acfs_vol

asmcmd voldelete -G ACFS_DG acfs_vol

asmcmd -p lsdg ACFS_DG

crsctl stat res -t | grep -i "acfs_dg"

crsctl stat res ora.acfs_dg.acfs_vol.acfs -t

Logs:

[root@oranode1 ~]# ps -ef|grep smon|grep -v 'grep\|grid'

oracle   13259     1  0 13:46 ?        00:00:00 asm_smon_+ASM1

oracle   13817     1  0 13:46 ?        00:00:00 ora_smon_DEVDB1

[root@oranode1 ~]#

[root@oranode1 ~]# lsmod |grep ora

oracleacfs           5173248  0

oracleadvm           1146880  0

oracleoks             753664  2 oracleadvm,oracleacfs

oracleasm              61440  1

[root@oranode1 ~]# crsctl stat res ora.ACFS_DG.dg -t

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

Name           Target  State        Server                   State details

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

Cluster Resources

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

ora.ACFS_DG.dg(ora.asmgroup)

      1        ONLINE  ONLINE       oranode1                 STABLE

      2        ONLINE  ONLINE       oranode2                 STABLE

      3        OFFLINE OFFLINE                               STABLE

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

[root@oranode1 ~]#

[root@oranode1 ~]#su - oracle

[oracle@oranode1 ~]$ asmcmd -p lsdg ACFS_DG

State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N         512             512   4096  4194304     40956    40780                0           40780              0             N  ACFS_DG/

[oracle@oranode1 ~]$

[root@oranode1 ~]# asmcmd volcreate -G ACFS_DG -s 10G acfs_vol

[root@oranode1 ~]# su - oracle

[oracle@oranode1 ~]$ asmcmd volinfo -G ACFS_DG acfs_vol

Diskgroup Name: ACFS_DG

         Volume Name: ACFS_VOL

         Volume Device: /dev/asm/acfs_vol-160

         State: ENABLED

         Size (MB): 10240

         Resize Unit (MB): 64

         Redundancy: UNPROT

         Stripe Columns: 8

         Stripe Width (K): 1024

         Usage:

         Mountpath:

[oracle@oranode1 ~]$

[root@oranode1 ~]# crsctl stat res -t | grep -i "advm"

ora.ACFS_DG.ACFS_VOL.advm

ora.proxy_advm

[root@oranode1 ~]#

[root@oranode1 ~]# crsctl stat res ora.ACFS_DG.ACFS_VOL.advm -t

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

Name           Target  State        Server                   State details

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

Local Resources

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

ora.ACFS_DG.ACFS_VOL.advm

               ONLINE  ONLINE       oranode1                 STABLE

               ONLINE  ONLINE       oranode2                 STABLE

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

[root@oranode1 ~]#

[root@oranode1 ~]# mkfs -t acfs /dev/asm/acfs_vol-160

mkfs.acfs: version                   = 19.0.0.0.0

mkfs.acfs: on-disk version           = 46.0

mkfs.acfs: volume                    = /dev/asm/acfs_vol-160

mkfs.acfs: volume size               = 10737418240  (  10.00 GB )

mkfs.acfs: Format complete.

[root@oranode1 ~]#

[root@oranode1 ~]# su - oracle

[root@oranode1 ~]# mkdir -p /acfs_test

[root@oranode1 ~]# chown oracle:oinstall /acfs_test_mount_point

[root@oranode2 ~]# su - oracle

[root@oranode2 ~]# mkdir -p /acfs_test

[root@oranode2 ~]# chown oracle:oinstall /acfs_test_mount_point

[root@oranode1 ~]# /sbin/acfsutil registry -a  /dev/asm/acfs_vol-160 /acfs_test_mount_point -u oracle

acfsutil registry: mount point /acfs_test_mount_point successfully added to Oracle Registry

[root@oranode1 ~]#

[root@oranode1 ~]# crsctl stat res -t | grep -i "acfs_dg"

ora.ACFS_DG.ACFS_VOL.advm

ora.acfs_dg.acfs_vol.acfs

ora.ACFS_DG.dg(ora.asmgroup)

[root@oranode1 ~]# crsctl stat res ora.acfs_dg.acfs_vol.acfs -t

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

Name           Target  State        Server                   State details

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

Local Resources

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

ora.acfs_dg.acfs_vol.acfs

               ONLINE  ONLINE       oranode1                 mounted on /acfs_tes

                                                             t_mount_point,STABLE

               ONLINE  ONLINE       oranode2                 mounted on /acfs_tes

                                                             t_mount_point,STABLE

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

[root@oranode1 ~]#

[root@oranode1 ~]# srvctl status filesystem -d /dev/asm/acfs_vol-160

ACFS file system /acfs_test_mount_point is mounted on nodes oranode1,oranode2

[root@oranode1 ~]# srvctl stop filesystem -d /dev/asm/acfs_vol-160

[root@oranode1 ~]# srvctl start filesystem -d /dev/asm/acfs_vol-160

[root@oranode1 ~]# srvctl status filesystem -d /dev/asm/acfs_vol-160

ACFS file system /acfs_test_mount_point is mounted on nodes oranode1,oranode2

[root@oranode1 ~]#

[root@oranode1 ~]# df -h /acfs_test_mount_point

Filesystem             Size  Used Avail Use% Mounted on

/dev/asm/acfs_vol-160   10G  570M  9.5G   6% /acfs_test_mount_point

[root@oranode1 ~]#

[root@oranode2 ~]# df -h /acfs_test_mount_point

Filesystem             Size  Used Avail Use% Mounted on

/dev/asm/acfs_vol-160   10G  570M  9.5G   6% /acfs_test_mount_point

[root@oranode2 ~]#

[oracle@oranode1 ~]$ asmcmd volinfo -G ACFS_DG acfs_vol

Diskgroup Name: ACFS_DG

         Volume Name: ACFS_VOL

         Volume Device: /dev/asm/acfs_vol-160

         State: ENABLED

         Size (MB): 10240

         Resize Unit (MB): 64

         Redundancy: UNPROT

         Stripe Columns: 8

         Stripe Width (K): 1024

         Usage: ACFS

         Mountpath: /acfs_test_mount_point

[oracle@oranode1 ~]$

[root@oranode1 ~]# srvctl config filesystem

Volume device: /dev/asm/acfs_vol-160

Diskgroup name: acfs_dg

Volume name: acfs_vol

Canonical volume device: /dev/asm/acfs_vol-160

Accelerator volume devices:

Mountpoint path: /acfs_test_mount_point

Mount point owner: oracle

Mount point group: oinstall

Mount permissions: owner:oracle:rwx,pgrp:oinstall:r-x,other::r-x

Mount users:

Type: ACFS

Mount options:

Description:

ACFS file system is enabled

ACFS file system is individually enabled on nodes:

ACFS file system is individually disabled on nodes:

[root@oranode1 ~]#

[root@oranode1 ~]# acfsutil size +1G -d /dev/asm/acfs_vol-160 /acfs_test_mount_point

acfsutil size: new file system size: 11811160064 (11264MB)

[root@oranode1 ~]#

[root@oranode1 ~]# su - oracle

[oracle@oranode1 ~]$ asmcmd volinfo -G ACFS_DG acfs_vol

Diskgroup Name: ACFS_DG

         Volume Name: ACFS_VOL

         Volume Device: /dev/asm/acfs_vol-160

         State: ENABLED

         Size (MB): 11264

         Resize Unit (MB): 64

         Redundancy: UNPROT

         Stripe Columns: 8

         Stripe Width (K): 1024

         Usage: ACFS

         Mountpath: /acfs_test_mount_point

[oracle@oranode1 ~]$ asmcmd -p lsdg ACFS_DG

State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N         512             512   4096  4194304     40956    29504                0           29504              0             N  ACFS_DG/

[oracle@oranode1 ~]$

[root@oranode1 ~]# df -h /acfs_test_mount_point

Filesystem             Size  Used Avail Use% Mounted on

/dev/asm/acfs_vol-160   11G  572M   11G   6% /acfs_test_mount_point

[root@oranode1 ~]#

[root@oranode2 ~]# df -h /acfs_test_mount_point

Filesystem             Size  Used Avail Use% Mounted on

/dev/asm/acfs_vol-160   11G  572M   11G   6% /acfs_test_mount_point

[root@oranode2 ~]#

[root@oranode1 ~]# srvctl stop filesystem -d /dev/asm/acfs_vol-160

[root@oranode1 ~]# /sbin/acfsutil rmfs /dev/asm/acfs_vol-160

[root@oranode1 ~]# su - oracle

[oracle@oranode1 ~]$ asmcmd voldisable -G ACFS_DG acfs_vol

[root@oranode1 ~]# asmcmd voldelete -G ACFS_DG acfs_vol

[root@oranode1 ~]# su - oracle

[oracle@oranode1 ~]$ asmcmd -p lsdg ACFS_DG

State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N         512             512   4096  4194304     40956    40772                0           40772              0             N  ACFS_DG/

[oracle@oranode1 ~]$

[root@oranode1 ~]# crsctl stat res -t | grep -i "acfs_dg"

ora.ACFS_DG.dg(ora.asmgroup)

[root@oranode1 ~]#

Regards,

Mallik

ACFS-9459: ADVM/ACFS is not supported on this OS version: '4.14.35-1902.300.11.el7uek.x86_64'

ACFS-9459: ADVM/ACFS is not supported on this OS version: '4.14.35-1902.300.11.el7uek.x86_64'

ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1)

Why ACFS?

1. Oracle Automatic Storage Management Cluster File System (Oracle ACFS) is a multi-platform, scalable file system, and storage management technology that extends Oracle Automatic Storage Management (Oracle ASM) functionality to support customer files maintained outside of Oracle Database. 

2. Oracle ACFS supports many database and application files, including executables, database trace files, database alert logs, application reports, BFILEs, and configuration files. Other supported files are video, audio, text, images, engineering drawings, and other general-purpose application file data.

3. Oracle ACFS does not support files for the Oracle Grid Infrastructure home.

4. Oracle ACFS does not support Oracle Cluster Registry (OCR) and voting files.

5. Oracle ACFS functionality requires that the disk group compatibility attributes for ASM and ADVM be set to 11.2 or greater.

Default Oracle 19c Base release was not installed with ACFS drivers, While creating ACFS volume we get error, We need to apply the any RU patch to make use of the ACFS feature.

[root@oranode1 ~]# cd /u01/app/19.0.0.0/grid/bin/

[root@oranode1 bin]# ./acfsdriverstate supported

ACFS-9459: ADVM/ACFS is not supported on this OS version: '4.14.35-1902.300.11.el7uek.x86_64'

ACFS-9201: Not Supported

ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf

[root@oranode1 bin]#

[root@oranode1 ~]# lsmod | grep ora

oracleasm              61440  1

[root@oranode1 ~]#

[oracle@oranode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM1

The Oracle base has been set to /u01/app/oracle

[oracle@oranode1 ~]$ /u01/app/19.0.0.0/grid/OPatch/opatch lspatches

29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

29517247;ACFS RELEASE UPDATE 19.3.0.0.0 (29517247)

29517242;Database Release Update : 19.3.0.0.190416 (29517242)

29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)

OPatch succeeded.

[oracle@oranode1 ~]$

[root@oranode1 bin]# ps -ef|grep smon |grep -v 'grep\|grid'

oracle   24378     1  0 12:49 ?        00:00:00 asm_smon_+ASM1

oracle   26072     1  0 12:49 ?        00:00:00 ora_smon_DEVDB1

[root@oranode1 bin]#

[root@oranode2 ~]# ps -ef|grep smon |grep -v 'grep\|grid'

oracle     716     1  0 12:49 ?        00:00:00 ora_smon_DEVDB2

oracle   30629     1  0 12:49 ?        00:00:00 asm_smon_+ASM2

[root@oranode2 ~]#

[root@oranode1 bin]# olsnodes

oranode1

oranode2

[root@oranode1 bin]#

[root@oranode1 ~]# which acfsutil

/usr/bin/which: no acfsutil in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/u01/app/19.0.0.0/grid/bin)

[root@oranode1 ~]#

[root@oranode1 ~]# acfsutil

bash: acfsutil: command not found...

[root@oranode1 ~]#

[root@oranode1 ~]# cd /u01/app/19.0.0.0/grid/bin/

[root@oranode1 bin]# ./acfsdriverstate supported

ACFS-9459: ADVM/ACFS is not supported on this OS version: '4.14.35-1902.300.11.el7uek.x86_64'

ACFS-9201: Not Supported

ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf

[root@oranode1 bin]#

[root@oranode1 ~]# acfsroot install

ACFS-9459: ADVM/ACFS is not supported on this OS version: '4.14.35-1902.300.11.el7uek.x86_64'

[root@oranode1 ~]#

[root@oranode1 bin]# uname -r

4.14.35-1902.300.11.el7uek.x86_64

[root@oranode1 bin]#

After the patching acfsutil commands are working fine:

[oracle@oranode1 ~]$ /u01/app/19.0.0.0/grid/OPatch/opatch lspatches

34580338;TOMCAT RELEASE UPDATE 19.0.0.0.0 (34580338)

34444834;OCW RELEASE UPDATE 19.17.0.0.0 (34444834)

34428761;ACFS RELEASE UPDATE 19.17.0.0.0 (34428761)

34419443;Database Release Update : 19.17.0.0.221018 (34419443)

33575402;DBWLM RELEASE UPDATE 19.0.0.0.0 (33575402)

OPatch succeeded.

[oracle@oranode1 ~]$

[root@oranode1 ~]# which acfsutil

/usr/sbin/acfsutil

[root@oranode1 ~]# acfsutil

acfsutil: Version 19.0.0.0.0

The following commands are supported:

     accel       - Manage accelerators

     audit       - Manage Auditing

     blog        - Manage Binary Logs

     cluster     - Display and manage cluster information

     compat      - Manage compatibility levels

     compress    - Manage compression

     defrag      - Defragment a file system

     dumpstate   - Dump file system state for diagnosis

     encr        - Manage Encryption

     info        - Display file system information

     log         - Manage Kernel Logs

     meta        - Collect file system metadata

     plugin      - Manage plugins

     registry    - Add, delete, or display mount registry entries

     remote      - Manage ACFS remote files

     repl        - Manage Replication

     rmfs        - Remove a file system

     sec         - Manage security

     size        - Resize a file system

     scrub       - Check mirror consistency

     snap        - Manage Snapshots

     tag         - Manage Tags

     tune        - Modify or display tunable parameters

     version     - Display version information

     freeze      - Suspend file system updates

     thaw        - Resume file system updates

     lockstats   - Print lock contention statistics

For more information, run:  acfsutil -h <command>

[root@oranode1 ~]# cd /u01/app/19.0.0.0/grid/bin/

[root@oranode1 bin]# ./acfsdriverstate supported

ACFS-9544: Invalid files or directories found: 'missing[], extra[4.1.12]'

ACFS-9200: Supported

[root@oranode1 bin]# lsmod |grep ora

oracleacfs           5173248  0

oracleadvm           1146880  0

oracleoks             753664  2 oracleadvm,oracleacfs

oracleasm              61440  1

[root@oranode1 bin]#

In case lsmod did not show oracleadvm & oracleacfs modeues then perform the below command (Not Mandatory)

[root@oranode1 bin]# acfsroot install

ACFS-9544: Invalid files or directories found: 'missing[], extra[4.1.12]'

ACFS-9300: ADVM/ACFS distribution files found.

ACFS-9314: Removing previous ADVM/ACFS installation.

ACFS-9315: Previous ADVM/ACFS components successfully removed.

ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf

ACFS-9307: Installing requested ADVM/ACFS software.

ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf

ACFS-9308: Loading installed ADVM/ACFS drivers.

ACFS-9321: Creating udev for ADVM/ACFS.

ACFS-9323: Creating module dependencies - this may take some time.

ACFS-9154: Loading 'oracleoks.ko' driver.

ACFS-9154: Loading 'oracleadvm.ko' driver.

ACFS-9154: Loading 'oracleacfs.ko' driver.

ACFS-9327: Verifying ADVM/ACFS devices.

ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.

ACFS-9156: Detecting control device '/dev/ofsctl'.

ACFS-9309: ADVM/ACFS installation correctness verified.

[root@oranode1 bin]#

[root@oranode1 bin]# ./acfsdriverstate supported

ACFS-9544: Invalid files or directories found: 'missing[], extra[4.1.12]'

ACFS-9200: Supported

[root@oranode1 bin]#

Regards,

Mallik

DB_UNKNOWN directory was created when using asmcmd pwcopy

DB_UNKNOWN directory was created when using asmcmd pwcopy:

'DB_UNKNOWN' directory was created when using asmcmd pwcopy (Doc ID 2329386.1)

1. Able to connect to primary RAC (RAC12C1 & RAC12C2) database as a sysdba user using scan name which indicates password file is working

[oracle@node1 ~]$ sqlplus sys/Mallik123@RAC12C as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:11:11 2023

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

Last Successful login time: Thu Jun 22 2023 21:52:47 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

2. Able to connect to primary RAC (RAC12C1 & RAC12C2) instance 1 as a sysdba user using scan name which indicates password file is working

[oracle@node1 ~]$ sqlplus sys/Mallik123@RAC12C1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:11:17 2023

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

Last Successful login time: Mon Jul 03 2023 12:11:12 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

3. Able to connect to primary RAC (RAC12C1 & RAC12C2) instance 2 as a sysdba user using scan name which indicates password file is working

[oracle@node1 ~]$ sqlplus sys/Mallik123@RAC12C2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:11:25 2023

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

Last Successful login time: Mon Jul 03 2023 12:11:18 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

4. Unable to connect to Standby RAC (RACSB1 & RACSB2) database as a sysdba user using scan name which indicates password file is not working

[oracle@node1 ~]$ sqlplus sys/Mallik123@RACSB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:11:33 2023

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

ERROR:

ORA-01017: invalid username/password; logon denied

ORA-17503: ksfdopn:2 Failed to open file +DATA/RACSB/PASSWORD/orapwracsb

ORA-15173: entry 'orapwracsb' does not exist in directory 'PASSWORD'

ORA-06512: at line 4

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 679

ORA-06512: at line 2

Enter user-name:

5. Unable to connect to Standby RAC (RACSB1 & RACSB2) instance 1 as a sysdba user using scan name which indicates password file is not working

[oracle@oraclenode1 script]$ sqlplus sys/Mallik123@RACSB1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:12:24 2023

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

ERROR:

ORA-01017: invalid username/password; logon denied

ORA-17503: ksfdopn:2 Failed to open file +DATA/RACSB/PASSWORD/orapwracsb

ORA-15173: entry 'orapwracsb' does not exist in directory 'PASSWORD'

ORA-06512: at line 4

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 679

ORA-06512: at line 2

Enter user-name: 

6. Unable to connect to Standby RAC (RACSB1 & RACSB2) instance 2 as a sysdba user using scan name which indicates password file is not working

[oracle@oraclenode1 script]$ sqlplus sys/Mallik123@RACSB2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:12:32 2023

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

ERROR:

ORA-01017: invalid username/password; logon denied

ORA-17503: ksfdopn:2 Failed to open file +DATA/RACSB/PASSWORD/orapwracsb

ORA-15173: entry 'orapwracsb' does not exist in directory 'PASSWORD'

ORA-06512: at line 4

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 679

ORA-06512: at line 2

Enter user-name: 

7. Verify the password file location 

[oracle@oraclenode1 script]$ srvctl config database -d RACSB

Database unique name: RACSB

Database name: RACSB

Oracle home: /u01/app/oracle/product/12.2.0.1/dbhome_1

Oracle user: oracle

Spfile: +DATA/RACSB/PARAMETERFILE/spfileRACSB.ora

Password file: +DATA/RACSB/PASSWORD/orapwRACSB >>>> Password file location

Domain:

Start options: open

Stop options: immediate

Database role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: RACSB1,RACSB2

Configured nodes: oraclenode1,oraclenode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@oraclenode1 script]$

8. We has password file copied from Primary to standby on local file system. We need to copy that to ASM disk group using pwcopy command.

[oracle@oraclenode1 dbs]$ asmcmd -p

ASMCMD [+] > pwcopy '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1' '+DATA/RACSB/PASSWORD/orapwRACSB'

copying /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1 -> +DATA/RACSB/PASSWORD/orapwRACSB

ASMCMD [+] > ls -l +DATA/RACSB/PASSWORD/orapwRACSB

Type      Redund  Striped  Time             Sys  Name

PASSWORD  UNPROT  COARSE   JUL 03 12:00:00  N    orapwRACSB => +DATA/DB_UNKNOWN/PASSWORD/pwddb_unknown.305.1141215603

ASMCMD [+] >

Note that if we don't us dbuniquename along with pwcopy then directory will created as DB_UNKNOWN

ASMCMD [+DATA] > rm -rf DB_UNKNOWN/

Note that below command failed due to environmental variable are pointing to GI home.

ASMCMD [+DATA] > pwcopy --dbuniquename RACSB '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1' '+DATA/RACSB/PASSWORD/orapwRACSB'

PRCD-1229 : An attempt to access configuration of database RACSB was rejected because its version 12.2.0.1.0 differs from the program version 19.0.0.0.0. Instead run the program from /u01/app/oracle/product/12.2.0.1/dbhome_1.

copying /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1 -> +DATA/RACSB/PASSWORD/orapwRACSB

ASMCMD-9453: failed to register password file as a CRS resource

ASMCMD [+DATA] > 

[oracle@oraclenode1 dbs]$ . oraenv

ORACLE_SID = [+ASM1] ? RACSB1

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

[oracle@oraclenode1 dbs]$ asmcmd -p

ASMCMD [+] > pwcopy --dbuniquename RACSB '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1' '+DATA/RACSB/PASSWORD/orapwRACSB'

ASMCMD [+] > exit

[oracle@oraclenode1 dbs]$

[oracle@oraclenode1 dbs]$ sqlplus sys/Mallik123@RACSB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:24:58 2023

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oraclenode1 dbs]$ sqlplus sys/Mallik123@RACSB1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:25:09 2023

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

Last Successful login time: Thu Jun 22 2023 21:52:47 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oraclenode1 dbs]$ sqlplus sys/Mallik123@RACSB2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:25:16 2023

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

Last Successful login time: Mon Jul 03 2023 12:25:09 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oraclenode1 dbs]$

Regards,

Mallik

ORA-29339: tablespace block size string does not match configured block sizes

ORA-29339: tablespace block size string does not match configured block sizes

Issue: 

Unable to create a Tablespace with larger block size.

Error:

ORA-29339: tablespace block size string does not match configured block sizes

Cause:

The block size of the tablespace to be created does not match the block sizes configured in the database.

Note:

Currently database running with 8K block size, we are trying to create tablespace with 16K block size.

Solution:

Configure the appropriate cache for the block size of this tablespace using below parameter.

db_2k_cache_size

db_4k_cache_size

db_8k_cache_size

db_16k_cache_size

db_32K_cache_size

Logs:

[root@oraclelab1 ~]# su - oracle

Last login: Tue Jul 25 08:06:22 IST 2023 on pts/0

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEVDB

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 18:20:28 2023

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE

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

db_block_size                        integer     8192

SQL> select NAME from v$tablespace;

NAME

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

SYSAUX

SYSTEM

UNDOTBS1

USERS

TEMP

TEST2

TEST4

7 rows selected.

SQL> select NAME from v$datafile;

NAME

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

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_lcx8tlpt_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_lcx8votl_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_lcx8wgxt_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_lcx8wj14_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/test2.dbf

/u01/app/oracle/oradata/DEVDB/datafile/test4.dbf

6 rows selected.

SQL> set pages 100 lines 1000

SQL> set pages 1000 lines 1000

SQL> col tablespace_name format a16;

col file_name format a50;

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024

FROM DBA_DATA_FILES;

TABLESPACE_NAME  FILE_NAME                                          BYTES/1024/1024

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

SYSTEM           /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_syste            1020

                 m_lcx8tlpt_.dbf

SYSAUX           /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysau             580

                 x_lcx8votl_.dbf

USERS            /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users               5

                 _lcx8wj14_.dbf

UNDOTBS1         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undot             390

                 bs1_lcx8wgxt_.dbf

TEST2            /u01/app/oracle/oradata/DEVDB/datafile/test2.dbf               100

TEST4            /u01/app/oracle/oradata/DEVDB/datafile/test4.dbf               100

6 rows selected.

SQL> select NAME,BIGFILE from v$tablespace;

NAME                           BIG

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

SYSAUX                         NO

SYSTEM                         NO

UNDOTBS1                       NO

USERS                          NO

TEMP                           NO

TEST2                          NO

TEST4                          NO

7 rows selected.

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024,AUTOEXTENSIBLE FROM DBA_DATA_FILES;

TABLESPACE_NAME  FILE_NAME                                          BYTES/1024/1024 AUT

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

SYSTEM           /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_syste            1020 YES

                 m_lcx8tlpt_.dbf

SYSAUX           /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysau             580 YES

                 x_lcx8votl_.dbf

UNDOTBS1         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undot             390 YES

                 bs1_lcx8wgxt_.dbf

USERS            /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users               5 YES

                 _lcx8wj14_.dbf

TEST2            /u01/app/oracle/oradata/DEVDB/datafile/test2.dbf               100 YES

TEST4            /u01/app/oracle/oradata/DEVDB/datafile/test4.dbf               100 NO

6 rows selected.

While creating Tablespace with 16k block size which failed

SQL> CREATE TABLESPACE test_16k_ts DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/test_16k_ts01.dbf' SIZE 10M BLOCKSIZE 16384;

CREATE TABLESPACE test_16k_ts DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/test_16k_ts01.dbf' SIZE 10M BLOCKSIZE 16384

*

ERROR at line 1:

ORA-29339: tablespace block size 16384 does not match configured block sizes

Solution is to set 16k db_block size, for that I am setting db_16k_cache_size=112M

SQL> alter system set db_16k_cache_size=112M scope=spfile;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 3690985856 bytes

Fixed Size                  8903040 bytes

Variable Size             704643072 bytes

Database Buffers         2969567232 bytes

Redo Buffers                7872512 bytes

Database mounted.

Database opened.

SQL> show parameter db_16k_cache_size

NAME                                 TYPE        VALUE

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

db_16k_cache_size                    big integer 112M

SQL> CREATE TABLESPACE test_16k_ts DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/test_16k_ts01.dbf' SIZE 10M BLOCKSIZE 16384;

Tablespace created.

SQL>

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

TABLESPACE_NAME  BLOCK_SIZE

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

SYSTEM                 8192

SYSAUX                 8192

UNDOTBS1               8192

TEMP                   8192

USERS                  8192

TEST2                  8192

TEST4                  8192

TEST_16K_TS           16384

8 rows selected.

SQL>

Extra note:

A. How many default tablespaces are available in Oracle Database?

B. Can database user be part of Multiple Tablespaces in Oracle?

C. Can database user creates objects (example: Tables) into Multiple Tablespaces?

1. create an user and and user will default get assigned to default tablespace of a database (USERS)

SQL> create user mallik identified by mallik;

User created.

SQL> grant dba to mallik;

Grant succeeded.

SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME='MALLIK';

USERNAME     ACCOUNT_STATUS                   DEFAULT_TABLESPACE    TEMPORARY_TABLESPACE

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

MALLIK       OPEN                             USERS                  TEMP

SQL>

2. Can user creates table or ojects into multiple tablespaces?

Answer: Yes 

create table EMP (SLNO number, NAME varchar2(15));

create table EMP_16K (SLNO number, NAME varchar2(15)) tablespace test_16k_ts;

SQL> conn mallik/mallik

Connected.

SQL> show user

USER is "MALLIK"

SQL> create table EMP (SLNO number, NAME varchar2(15));

Table created.

SQL> create table EMP_16K (SLNO number, NAME varchar2(15)) tablespace test_16k_ts;

Table created.

SQL>

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME like 'EMP%';

OWNER     TABLE_NAME     TABLESPACE_NAME

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

MALLIK    EMP            USERS

MALLIK    EMP_16K        TEST_16K_TS

SQL>

3. Can we move a object from one tablespace to another tablespace?

Answer: Yes

SQL> alter table MALLIK.EMP move tablespace TEST_16K_TS;

Table altered.

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME like 'EMP%';

OWNER    TABLE_NAME TABLESPACE_NAME

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

MALLIK   EMP_16K    TEST_16K_TS

MALLIK   EMP        TEST_16K_TS

SQL>

Regards,

Mallik

Row chaining Vs Row migration

Row chaining Vs Row migration

Chained rows:

- Chained rows are records stored over multiple data blocks due to their excessive size. 

- Row piece size is grater than DB_BLOCK_SIZE then row piece will be spitted and spead accross the multiple blicks.

- Mainly caused due to INSERT statement

Migrated rows:

- Migrated rows occur when an UPDATE DML causes the rows to expand onto another data block.

- Mainly caused due to UPDATE statement

A migrated row is a special case of a chained row. 

A migrated row is a chained row, a chained row may or may not be a migrated row.

Chained rows:

Questions:

1. What is row chaining?

2. What is row migration?

3. How to identify the row chaining and row migration?

4. How to fix this row chaining and row migration?

Analyze the table to refresh the statistics:

analyze table EMP compute statistics;

This query will show how many chained (and migrated) rows each table has:

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;

Check for chained rows for perticuler table:

select chain_cnt from all_tables where owner='MALLIK' and TABLE_NAME='EMP';

In order to a avoide the row chaining we need to create a tablespace with bigger block size and move the table:

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

TABLESPACE_NAME  BLOCK_SIZE

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

SYSTEM                 8192

SYSAUX                 8192

UNDOTBS1           8192

TEMP                    8192

USERS                  8192

TEST_16K_TS      16384 >>> 16K block size

alter table MALLIK.EMP move tablespace TEST_16K_TS;

After table move we should rebuild the index since index become unusable:

alter index EMP.PK_EMPID rebuild;

After moved table analyze the table to refresh the statistics and and check for row chaining which will be resolved. 

analyze table EMP compute statistics;

select chain_cnt from all_tables where owner='MALLIK' and TABLE_NAME='EMP';

Migrated rows:

This will put the rows into the CHAINED_ROWS table which is created by the utlchain.sql script 

($ORACLE_HOME/rdbms/admin).

They create a table named CHAINED_ROWS in the schema of the user submitting the script.

SELECT * FROM chained_rows;

Example:

create table CHAINED_ROWS (

owner_name varchar2(30),

table_name varchar2(30),

cluster_name varchar2(30),

partition_name varchar2(30),

subpartition_name varchar2(30),

head_rowid rowid,

analyze_timestamp date

);

To see which rows are chained:

ANALYZE TABLE EMP LIST CHAINED ROWS;

select owner_name, table_name, head_rowid from chained_rows;

The following query can be used to identify tables with chaining problems:

TTITLE 'Tables Experiencing Chaining'

SELECT owner, table_name,

       NVL(chain_cnt,0) "Chained Rows"

  FROM all_tables

 WHERE owner NOT IN ('SYS', 'SYSTEM')

       AND NVL(chain_cnt,0) > 0

ORDER BY owner, table_name;

Conclusion

- Row migration is typically caused by UPDATE operation

- Row chaining is typically caused by INSERT operation.

- SQL statements which are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.

- To diagnose chained/migrated rows use ANALYZE command , query V$SYSSTAT view

- To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE

Regards,

Mallik

PDB save state?

Automate Pluggable Database opening during Container instance startup

1) List the PDBs and respective Modes 

sqlplus / as sysdba

SQL> show pdbs

2) Check the Saved State for all PDB’s from DBA view

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

3) Check the Saved State for all PDB’s from CDB view

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

4) Change Save State of PDB by usig below statement

alter pluggable database PDB1 save state;

5) To discard any saved state of a pluggable database using below statement

alter pluggable database PDB1 discard state;

logs:

=====

[root@oracleprod ~]# su - oracle

Last login: Wed Jun 21 01:20:29 +08 2023

[oracle@oracleprod ~]$

[oracle@oracleprod ~]$ ps -ef|grep smon

oracle   17433 16853  0 01:45 pts/0    00:00:00 grep --color=auto smon

oracle   29594     1  0 Jun19 ?        00:00:02 ora_smon_PRODCDB

[oracle@oracleprod ~]$ . oraenv

ORACLE_SID = [PRODCDB] ?

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

[oracle@oracleprod ~]$

[oracle@oracleprod ~]$ env |grep ORA

ORACLE_SID=PRODCDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oracleprod ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 21 01:47:00 2023

Version 19.19.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         6 ORAODB1                        READ WRITE NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

[oracle@oracleprod ~]$

[oracle@oracleprod ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 21 01:47:57 2023

Version 19.19.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

[oracle@oracleprod ~]$ ps -ef|grep smon

oracle   18270 16853  0 01:48 pts/0    00:00:00 grep --color=auto smon

[oracle@oracleprod ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 21 01:48:53 2023

Version 19.19.0.0.0

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

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 3707763824 bytes

Fixed Size                  9170032 bytes

Variable Size            1912602624 bytes

Database Buffers         1778384896 bytes

Redo Buffers                7606272 bytes

Database mounted.

Database opened.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           MOUNTED

         4 PDB2                           MOUNTED

         6 ORAODB1                        MOUNTED

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           MOUNTED

         6 ORAODB1                        MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         6 ORAODB1                        READ WRITE NO

SQL> alter pluggable database ORAODB1 close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         6 ORAODB1                        MOUNTED

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

no rows selected

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

no rows selected

SQL> alter pluggable database PDB1 save state;

Pluggable database altered.

SQL> col CON_NAME for a20;

SQL> col INSTANCE_NAME for a20;

SQL> col state for a20;

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

CON_NAME             INSTANCE_NAME        STATE

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

PDB1                 PRODCDB              OPEN

SQL> col name for a20;

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

NAME                 STATE

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

PDB1                 OPEN

SQL> alter pluggable database PDB2 save state;

Pluggable database altered.

SQL> col name for a20;

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

NAME                 STATE

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

PDB1                 OPEN

PDB2                 OPEN

SQL> alter pluggable database ORAODB1 save state;

Pluggable database altered.

SQL> col name for a20;

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

NAME                 STATE

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

PDB1                 OPEN

PDB2                 OPEN

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 3707763824 bytes

Fixed Size                  9170032 bytes

Variable Size            1912602624 bytes

Database Buffers         1778384896 bytes

Redo Buffers                7606272 bytes

Database mounted.

Database opened.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

         6 ORAODB1                        MOUNTED

SQL> col name for a20;

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

NAME                 STATE

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

PDB1                 OPEN

PDB2                 OPEN

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

CON_NAME             INSTANCE_NAME        STATE

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

PDB1                 PRODCDB              OPEN

PDB2                 PRODCDB              OPEN

SQL> alter pluggable database PDB1 discard state;

Pluggable database altered.

SQL> col CON_NAME for a20;

SQL> col INSTANCE_NAME for a20;

SQL> col state for a20;

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

CON_NAME             INSTANCE_NAME        STATE

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

PDB2                 PRODCDB              OPEN

SQL> alter pluggable database PDB2 discard state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

no rows selected

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

[oracle@oracleprod ~]$

Regards,

Mallikarjun Ramadurg

WhatsApp: +91 9880616848

gmail: mallikarjun.ramadurg@gmail.com