Mallik034: 2023

Friday, September 15, 2023

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

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

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

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

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

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

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

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

Monday, August 14, 2023

Unable to connect to PDB?

Issue: Same PDB name on 2 CDBs and tns connection will be in trouble.

12c Multitenant: How to Connect to PDBs with Same Name in Different CDBs (Doc ID 2123106.1)

Solution: Include INSTANCE_NAME in TNS entries

DEVCDB (PDB1)

sqlplus user1/user1@PDB1_DEVCDB

sqlplus mallik/mallik@PDB1_DEVCDB

TESTCDB(PDB1)

sqlplus user2/user2@PDB1_TESTCDB

sqlplus mallik/mallik@PDB1_TESTCDB

Traditional TNS:

PDB1_DEVCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PDB1) (INSTANCE_NAME=DEVCDB)

)

PDB1_TESTCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PDB1)(INSTANCE_NAME=TESTCDB)

)

Best Practice for TNS:

PDB1_DEVCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PDB1)(INSTANCE_NAME=DEVCDB)

)

PDB1_TESTCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PDB1)(INSTANCE_NAME=TESTCDB)

)

Best Practice for Setup:

One Database (Normal Database / CDB Database) - DEVCDB

One Oracle Home - /u01/app/oracle/product/19.0.0.0/dbhome_1

One Listener - LISTENER_DEVCDB (1521)

One Database (Normal Database / CDB Database) - TESTCDB

One Oracle Home - /u01/app/oracle/product/19.0.0.0/dbhome_2

One Listener - LISTENER_TESTCDB (1522)

Current Setup (Not followed Best Practice):

One Oracle Home - /u01/app/oracle/product/19.0.0.0/dbhome_1

One Listener - LISTENER (1251)

Multiple DBs (DEVCDB - PDB1 & TESTCDB - PDB1)

Best Practice for TNS:

PDB1_DEVCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PDB1)(INSTANCE_NAME=DEVCDB)

)

PDB1_TESTCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PDB1)(INSTANCE_NAME=TESTCDB)

)

[oracle@oraclelab1 admin]$ pwd

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

[oracle@oraclelab1 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@oraclelab1 admin]$ cat tnsnames.ora

DEVCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = DEVCDB)

)

TESTCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = TESTCDB)

)

PDB1_DEVCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PDB1)

)

PDB1_TESTCDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PDB1)

)

[oracle@oraclelab1 admin]$

Regards,

Mallik

Sunday, August 6, 2023

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 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

Wednesday, August 2, 2023

GI - Grid Infrastructure Maintenance on Restart Server

GI - Grid Infrastructure Maintenance for Restart Server

1. How to Stop and Start Database?

2. How to Stop and Start ASM Instance?

3. How to Stop and Start HAS?

Database:

==========

SQL> shut immediate

SQL> startup;

SQL> startup force;

$srvctl status database -d TESTDB

$srvctl config database -d TESTDB

$srvctl stop database -d TESTDB

$srvctl start database -d TESTDB

ASM:

====

SQL> shut immediate

SQL> startup;

$srvctl status asm

$srvctl config asm

$srvctl stop asm

$srvctl stop asm -f

$srvctl start asm

ASMCMD> shutdown ---immediate/--abort

ASMCMD> startup

HAS/OHASD:

==========

$crsctl check has

$crsctl stop has

$crsctl stop has -f

$crsctl start has

$crsctl stat res -t -init

#systemctl status ohasd

#systemctl stop ohasd

#systemctl start ohasd

##### Verify the Database and ASM Instances on Oracle Restart Server ######

[root@oraclelab2 ~]# ps -ef|grep smon

root 9222 9133 0 11:47 pts/5 00:00:00 grep --color=auto smon

oracle 28574 1 0 Aug01 ? 00:00:01 asm_smon_+ASM

oracle 28737 1 0 Aug01 ? 00:00:01 ora_smon_TESTDB

[root@oraclelab2 ~]#

[root@oraclelab2 ~]# su - oracle

Last login: Wed Aug 2 09:11:49 IST 2023 on pts/1

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM

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

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ srvctl config asm

ASM home: <CRS home>

Password file: +OCR/orapwasm

Backup of Password file:

ASM listener: LISTENER

Spfile: +OCR/ASM/ASMPARAMETERFILE/registry.253.1135130687

ASM diskgroup discovery string: /dev/oracleasm/disks/*

[oracle@oraclelab2 ~]$ srvctl status asm

ASM is running on oraclelab2

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ . oraenv

ORACLE_SID = [+ASM] ? TESTDB

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

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ env |grep ORA

ORACLE_SID=TESTDB

ORACLE_BASE=/u01/app/oracle

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

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ srvctl status database -d TESTDB

Database is running.

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ srvctl config database -d TESTDB

Database unique name: TESTDB

Database name: TESTDB

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

Oracle user: oracle

Spfile: +DATA/TESTDB/PARAMETERFILE/spfile.265.1143706501

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA,RECO

Services:

OSDBA group: oinstall

OSOPER group: oinstall

Database instance: TESTDB

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ ###Starting & Stopping Database####

[oracle@oraclelab2 ~]$ srvctl status database -d TESTDB

Database is running.

[oracle@oraclelab2 ~]$

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

oracle 9516 9227 0 11:48 pts/5 00:00:00 grep --color=auto smon

oracle 28574 1 0 Aug01 ? 00:00:01 asm_smon_+ASM

oracle 28737 1 0 Aug01 ? 00:00:01 ora_smon_TESTDB

[oracle@oraclelab2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 2 11:49:18 2023

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

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

Version 19.17.0.0.0

[oracle@oraclelab2 ~]$ srvctl stop database -d TESTDB

[oracle@oraclelab2 ~]$ srvctl status database -d TESTDB

Database is not running.

[oracle@oraclelab2 ~]$

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

oracle 9633 9227 0 11:49 pts/5 00:00:00 grep --color=auto smon

oracle 28574 1 0 Aug01 ? 00:00:01 asm_smon_+ASM

[oracle@oraclelab2 ~]$ srvctl start database -d TESTDB

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ srvctl status database -d TESTDB

Database is running.

[oracle@oraclelab2 ~]$

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

oracle 9775 1 0 11:50 ? 00:00:00 ora_smon_TESTDB

oracle 10125 9227 0 11:50 pts/5 00:00:00 grep --color=auto smon

oracle 28574 1 0 Aug01 ? 00:00:01 asm_smon_+ASM

[oracle@oraclelab2 ~]$

#### Starting & Stopping ASM which will impact Databases which are residing under ASM #####

[oracle@oraclelab2 ~]$ . oraenv

ORACLE_SID = [TESTDB] ? +ASM

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

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ env |grep ORA

ORACLE_SID=+ASM

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/19.0.0.0/grid

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ srvctl status asm

ASM is running on oraclelab2

[oracle@oraclelab2 ~]$ srvctl config asm

ASM home: <CRS home>

Password file: +OCR/orapwasm

Backup of Password file:

ASM listener: LISTENER

Spfile: +OCR/ASM/ASMPARAMETERFILE/registry.253.1135130687

ASM diskgroup discovery string: /dev/oracleasm/disks/*

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ srvctl stop asm

PRCR-1065 : Failed to stop resource ora.asm

CRS-2974: unable to act on resource 'ora.asm' on server 'oraclelab2' because that would require stopping or relocating resource 'ora.testdb.db' but the appropriate force flag was not specified

[oracle@oraclelab2 ~]$

# Above command failed due to fact that TESTDB database act as client connection to ASM #

# Use force option to stop ASM #

[oracle@oraclelab2 ~]$ srvctl stop asm -f

[oracle@oraclelab2 ~]$ srvctl status asm

ASM is not running.

[oracle@oraclelab2 ~]$

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

oracle 10496 9227 0 11:53 pts/5 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$

# We stopped ASM but underlying database TESTDB also went down, Once we start the ASM TESTDB database also will comes back online automatically #

[oracle@oraclelab2 ~]$ srvctl start asm

[oracle@oraclelab2 ~]$ srvctl status asm

ASM is running on oraclelab2

[oracle@oraclelab2 ~]$

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

oracle 10658 1 0 11:55 ? 00:00:00 asm_smon_+ASM

oracle 10806 1 0 11:55 ? 00:00:00 ora_smon_TESTDB

oracle 11201 9227 0 11:55 pts/5 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$

#### Starting & Stopping HAS will impact ASM as well as Database since both ASM and Database will run on top of HAS ####

# In order to stop ans start HAS we have use crsctl utility and run as root user #

[root@oraclelab2 ~]# . oraenv

ORACLE_SID = [root] ? +ASM

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

[root@oraclelab2 ~]#

[root@oraclelab2 ~]# env |grep ORA

ORACLE_SID=+ASM

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/19.0.0.0/grid

[root@oraclelab2 ~]#

[root@oraclelab2 ~]# crsctl check has

CRS-4638: Oracle High Availability Services is online

[root@oraclelab2 ~]#

[root@oraclelab2 ~]# crsctl stop has

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oraclelab2'

CRS-2673: Attempting to stop 'ora.testdb.db' on 'oraclelab2'

CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'oraclelab2'

CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'oraclelab2' succeeded

CRS-2677: Stop of 'ora.testdb.db' on 'oraclelab2' succeeded

CRS-2673: Attempting to stop 'ora.DATA.dg' on 'oraclelab2'

CRS-2677: Stop of 'ora.DATA.dg' on 'oraclelab2' succeeded

CRS-2673: Attempting to stop 'ora.DGROUPC.dg' on 'oraclelab2'

CRS-2673: Attempting to stop 'ora.OCR.dg' on 'oraclelab2'

CRS-2673: Attempting to stop 'ora.RECO.dg' on 'oraclelab2'

CRS-2677: Stop of 'ora.RECO.dg' on 'oraclelab2' succeeded

CRS-2677: Stop of 'ora.DGROUPC.dg' on 'oraclelab2' succeeded

CRS-2677: Stop of 'ora.OCR.dg' on 'oraclelab2' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'oraclelab2'

CRS-2677: Stop of 'ora.asm' on 'oraclelab2' succeeded

CRS-2673: Attempting to stop 'ora.evmd' on 'oraclelab2'

CRS-2677: Stop of 'ora.evmd' on 'oraclelab2' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'oraclelab2'

CRS-2677: Stop of 'ora.cssd' on 'oraclelab2' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oraclelab2' has completed

CRS-4133: Oracle High Availability Services has been stopped.

[root@oraclelab2 ~]# crsctl start has

CRS-4123: Oracle High Availability Services has been started.

[root@oraclelab2 ~]#

[root@oraclelab2 ~]# crsctl check has

CRS-4638: Oracle High Availability Services is online

[root@oraclelab2 ~]#

[root@oraclelab2 ~]# ps -ef|grep smon

oracle 11911 1 0 11:58 ? 00:00:00 asm_smon_+ASM

oracle 12062 1 0 11:59 ? 00:00:00 ora_smon_TESTDB

root 12913 12802 0 12:05 pts/6 00:00:00 grep --color=auto smon

[root@oraclelab2 ~]#

##### We can stop & start HAS using systemctl utility ####

[root@oraclelab2 ~]# systemctl status ohasd

● ohasd.service - LSB: Start and Stop Oracle High Availability Service

Loaded: loaded (/etc/rc.d/init.d/ohasd; bad; vendor preset: disabled)

Active: active (exited) since Fri 2023-06-16 19:15:25 IST; 1 months 16 days ago

Docs: man:systemd-sysv-generator(8)

Process: 3529 ExecStart=/etc/rc.d/init.d/ohasd start (code=exited, status=0/SUCCESS)

Tasks: 0

Memory: 0B

Jun 16 19:15:25 oraclelab2.localdomain.com systemd[1]: Starting LSB: Start and Stop Oracle High Availability Service...

Jun 16 19:15:25 oraclelab2.localdomain.com ohasd[3529]: Starting ohasd:

Jun 16 19:15:25 oraclelab2.localdomain.com su[3566]: (to oracle) root on none

Jun 16 19:15:25 oraclelab2.localdomain.com ohasd[3529]: CRS-4123: Oracle High Availability Services has been started.

Jun 16 19:15:25 oraclelab2.localdomain.com systemd[1]: Started LSB: Start and Stop Oracle High Availability Service.

[root@oraclelab2 ~]# systemctl stop ohasd

[root@oraclelab2 ~]# ps -ef|grep smon

root 19131 17810 0 13:32 pts/4 00:00:00 grep --color=auto smon

[root@oraclelab2 ~]# systemctl start ohasd

[root@oraclelab2 ~]# systemctl status ohasd

● ohasd.service - LSB: Start and Stop Oracle High Availability Service

Loaded: loaded (/etc/rc.d/init.d/ohasd; bad; vendor preset: disabled)

Active: active (exited) since Wed 2023-08-02 13:32:49 IST; 5s ago

Docs: man:systemd-sysv-generator(8)

Process: 19023 ExecStop=/etc/rc.d/init.d/ohasd stop (code=exited, status=0/SUCCESS)

Process: 19172 ExecStart=/etc/rc.d/init.d/ohasd start (code=exited, status=0/SUCCESS)

Aug 02 13:32:49 oraclelab2.localdomain.com systemd[1]: Starting LSB: Start and Stop Oracle High Availability Service...

Aug 02 13:32:49 oraclelab2.localdomain.com ohasd[19172]: Starting ohasd:

Aug 02 13:32:49 oraclelab2.localdomain.com su[19199]: (to oracle) root on none

Aug 02 13:32:49 oraclelab2.localdomain.com ohasd[19172]: CRS-4123: Oracle High Availability Services has been started.

Aug 02 13:32:49 oraclelab2.localdomain.com systemd[1]: Started LSB: Start and Stop Oracle High Availability Service.

[root@oraclelab2 ~]# ps -ef|grep smon

root 19587 17810 0 13:33 pts/4 00:00:00 grep --color=auto smon

[root@oraclelab2 ~]# ps -ef|grep smon

oracle 19691 1 0 13:33 ? 00:00:00 asm_smon_+ASM

root 19724 17810 0 13:33 pts/4 00:00:00 grep --color=auto smon

[root@oraclelab2 ~]#

[root@oraclelab2 ~]# ps -ef|grep smon

oracle 19691 1 0 13:33 ? 00:00:00 asm_smon_+ASM

oracle 19853 1 0 13:33 ? 00:00:00 ora_smon_TESTDB

root 21350 17810 0 13:50 pts/4 00:00:00 grep --color=auto smon

[root@oraclelab2 ~]#

Extra Note:

1. Stop & Start ASM instance using SQL command line:

[oracle@oraclelab2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 2 13:53:09 2023

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> shut immediate; >>>>>>>>>>>>>>>>>>> In case immediate did not work then we can use abort

ASM diskgroups volume disabled

ASM diskgroups dismounted

ASM instance shutdown

SQL>

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

oracle 21845 21370 0 13:54 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 2 13:54:11 2023

Version 19.17.0.0.0

Connected to an idle instance.

SQL> startup;

ASM instance started

Total System Global Area 1137173320 bytes

Fixed Size 8905544 bytes

Variable Size 1103101952 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

ASM diskgroups volume enabled

SQL> exit

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

Version 19.17.0.0.0

[oracle@oraclelab2 ~]$

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

oracle 21914 1 0 13:54 ? 00:00:00 asm_smon_+ASM

oracle 22053 21370 0 13:55 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$

2. Stop & Start ASM instance using ASMCMD command line:

ASMCMD [+] > shutdown --immediate

ASM diskgroups volume disabled

ASM diskgroups dismounted

ASM instance shutdown

Connected to an idle instance.

ASMCMD [+] > exit

[oracle@oraclelab2 ~]$

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

oracle 22669 21370 0 13:58 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ asmcmd -p

Connected to an idle instance.

ASMCMD [+] > startup;

ASM instance started

Total System Global Area 1137173320 bytes

Fixed Size 8905544 bytes

Variable Size 1103101952 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

ASM diskgroups volume enabled

ASMCMD [+] > exit

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

oracle 22859 1 0 13:59 ? 00:00:00 asm_smon_+ASM

oracle 22987 21370 0 13:59 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$

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

oracle 22859 1 0 13:59 ? 00:00:00 asm_smon_+ASM

oracle 23938 1 0 14:11 ? 00:00:00 ora_smon_TESTDB

oracle 24249 21370 0 14:11 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$

3. Stop & Start Database using SQL command line or using srvctl utility

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

oracle 24762 1 0 14:13 ? 00:00:00 asm_smon_+ASM

oracle 25212 1 0 14:16 ? 00:00:00 ora_smon_TESTDB

oracle 27293 21370 0 14:42 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$ srvctl status database -d TESTDB

[oracle@oraclelab2 ~]$ srvctl stop database -d TESTDB

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

oracle 24762 1 0 14:13 ? 00:00:00 asm_smon_+ASM

oracle 25052 21370 0 14:15 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ srvctl start database -d TESTDB

[oracle@oraclelab2 ~]$ srvctl status database -d TESTDB

Database is running.

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

oracle 24762 1 0 14:13 ? 00:00:00 asm_smon_+ASM

oracle 25212 1 0 14:16 ? 00:00:00 ora_smon_TESTDB

oracle 27293 21370 0 14:42 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 2 14:43:55 2023

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.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.17.0.0.0

[oracle@oraclelab2 ~]$

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

oracle 24762 1 0 14:13 ? 00:00:00 asm_smon_+ASM

oracle 27415 21370 0 14:44 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 2 14:44:37 2023

Version 19.17.0.0.0

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 3707763120 bytes

Fixed Size 8903088 bytes

Variable Size 721420288 bytes

Database Buffers 2969567232 bytes

Redo Buffers 7872512 bytes

Database mounted.

Database opened.

SQL> exit

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

Version 19.17.0.0.0

[oracle@oraclelab2 ~]$

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

oracle 24762 1 0 14:13 ? 00:00:00 asm_smon_+ASM

oracle 27507 1 0 14:44 ? 00:00:00 ora_smon_TESTDB

oracle 28042 21370 0 14:46 pts/4 00:00:00 grep --color=auto smon

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ srvctl status database -d TESTDB

Database is running.

[oracle@oraclelab2 ~]$

Regards,

Mallik

Monday, July 31, 2023

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

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

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

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

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

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

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

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

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

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

Friday, September 15, 2023

RMAN Status Query!!!

#Calculate the RMAN backup size

#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.

#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.

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

Log:

Monday, August 14, 2023

Unable to connect to PDB?

Issue: Same PDB name on 2 CDBs and tns connection will be in trouble.

Solution: Include INSTANCE_NAME in TNS entries

DEVCDB (PDB1)

TESTCDB(PDB1)

Best Practice for Setup:

Current Setup (Not followed Best Practice):

Best Practice for TNS:

Sunday, August 6, 2023

ACFS Administration (Create, Increase, Delete)

1. Verify the ACFS Modules Installed:

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

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

4. Verify the ACFS volume information

5. Check acfs volume services at cluster side

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

7. Register the ACFS device to oracle as a owner

8. Use srvctl command to mount and unmount ACFS filesystem

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

10. Verify the configuration of ACFS filesystem

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

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

Logs:

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)

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.

After the patching acfsutil commands are working fine:

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

Wednesday, August 2, 2023

GI - Grid Infrastructure Maintenance for Restart Server

##### Verify the Database and ASM Instances on Oracle Restart Server ######

[oracle@oraclelab2 ~]$ ###Starting & Stopping Database####

#### Starting & Stopping ASM which will impact Databases which are residing under ASM #####

##### We can stop & start HAS using systemctl utility ####

1. Stop & Start ASM instance using SQL command line:

2. Stop & Start ASM instance using ASMCMD command line:

3. Stop & Start Database using SQL command line or using srvctl utility

Monday, July 31, 2023

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

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

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

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

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

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

7. Verify the password file location

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.

#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.