OracleDBAtalk

Thursday, August 29, 2024

RAC : 11gR2 Clusterware Startup Sequence

Here is the brief explanation that how the clusterware brings up step by step .

1. When a node of an Oracle Clusterware cluster start/restarts, OHASD is started by platform-specific means. OHASD is the root for bringing up Oracle Clusterware. OHASD has access to the OLR (Oracle Local Registry) stored on the local file system. OLR provides needed data to complete OHASD initialization.

2. OHASD brings up GPNPD and CSSD. CSSD has access to the GPNP Profile stored on the local file system. This profile contains the following vital bootstrap data;

a. ASM Diskgroup Discovery String

b. ASM SPFILE location (Diskgroup name)

c. Name of the ASM Diskgroup containing the Voting Files

3. The Voting Files locations on ASM Disks are accessed by CSSD with well-known pointers in the ASM Disk headers and CSSD is able to complete initialization and start or join an existing cluster.

4. OHASD starts an ASM instance and ASM can now operate with CSSD initialized and operating. The ASM instance uses special code to locate the contents of the ASM SPFILE, assuming it is stored in a Diskgroup.

5. With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRSD.

6. OHASD starts CRSD with access to the OCR in an ASM Diskgroup.

7. Clusterware completes initialization and brings up other services under its control.

When Clusterware starts three files are involved.

1. OLR – Is the first file to be read and opened. This file is local and this file contains information regarding where the voting disk is stored
and information to startup the ASM. (e.g ASM DiscoveryString)

2. VOTING DISK – This is the second file to be opened and read, this is dependent on only OLR being accessible.

ASM starts after CSSD or ASM does not start if CSSD is offline (i.e voting file missing)

How are Voting Disks stored in ASM?

Voting disks are placed directly on ASMDISK. Oracle Clusterware will store the votedisk on the disk within a disk group that holds the Voting Files.
Oracle Clusterware does not rely on ASM to access the Voting Files, which means Oracle Clusterware does not need of Diskgroup to read and write on ASMDISK. It is possible to check for existence of voting files on a ASMDISK using the V$ASM_DISK column VOTING_FILE.
So, voting files not depend of Diskgroup to be accessed, does not mean that the diskgroup is not needed, diskgroup and voting file are linked by their settings.

3. OCR – Finally the ASM Instance starts and mount all Diskgroups, then Clusterware Deamon (CRSD) opens and reads the OCR which is stored on Diskgroup.

So, if ASM already started, ASM does not depend on OCR or OLR to be online. ASM depends on CSSD (Votedisk) to be online.

There is a exclusive mode to start ASM without CSSD (but it’s to restore OCR or VOTE purposes)

As per Oracle Documentation

The full description, the really unreadable diagram and/or any updates to this you can find it in MOS Document 1053147.1

Click on the image below to see the full size

Level 1: OHASD Spawns:

cssdagent – Agent responsible for spawning CSSD.
orarootagent – Agent responsible for managing all root owned ohasd resources.
oraagent – Agent responsible for managing all oracle owned ohasd resources.
cssdmonitor – Monitors CSSD and node health (along wth the cssdagent).

Level 2: OHASD rootagent spawns:

CRSD – Primary daemon responsible for managing cluster resources.
CTSSD – Cluster Time Synchronization Services Daemon
Diskmon
ACFS (ASM Cluster File System) Drivers

Level 2: OHASD oraagent spawns:

MDNSD – Used for DNS lookup
GIPCD – Used for inter-process and inter-node communication
GPNPD – Grid Plug & Play Profile Daemon
EVMD – Event Monitor Daemon
ASM – Resource for monitoring ASM instances

Level 3: CRSD spawns:

orarootagent – Agent responsible for managing all root owned crsd resources.
oraagent – Agent responsible for managing all oracle owned crsd resources.

Level 4: CRSD rootagent spawns:

Network resource – To monitor the public network
SCAN VIP(s) – Single Client Access Name Virtual IPs
Node VIPs – One per node
ACFS Registery – For mounting ASM Cluster File System
GNS VIP (optional) – VIP for GNS

Level 4: CRSD oraagent spawns:

ASM Resouce – ASM Instance(s) resource
Diskgroup – Used for managing/monitoring ASM diskgroups.
DB Resource – Used for monitoring and managing the DB and instances
SCAN Listener – Listener for single client access name, listening on SCAN VIP
Listener – Node listener listening on the Node VIP
Services – Used for monitoring and managing services
ONS – Oracle Notification Service
eONS – Enhanced Oracle Notification Service
GSD – For 9i backward compatibility
GNS (optional) – Grid Naming Service – Performs name resolution

List the GI Processes

Saturday, August 10, 2024

REORG STEPS:

Step 1 :

Create a new tablespace;

CREATE BIGFILE TABLESPACE CDMDM_NEW DATAFILE '+SPARSE' SIZE 20G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

Step 2 :

Move all tables to new tablespace by below dynamic query :

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE ONLINE TABLESPACE CDMDM_NEW compress for Archive high;' from dba_tables where owner='CDMDM' ;

****

Partition tables also need to move to new tablespace.

select 'ALTER TABLE '||a.OWNER||'.'||a.TABLE_NAME||' move PARTITION '||b.PARTITION_NAME||' TABLESPACE CDMDM_NEW compress for Archive high;' from dba_part_tables a, dba_segments b where a.table_name=b.segment_name and a.owner='CDMDM' and b.tablespace_name='CDMDM';

select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' move PARTITION '||PARTiTION_NAME||' TABLESPACE NONGWDATA1;' from dba_tab_partitions where tablespace_name in ('NONGWDATA_NEW','NONGWDATA');

****
If there Bitmap Indexes presents , 1st need to unusable those indexes. Otherwise it will fail .

select 'alter INDEX '||OWNER||'.'||INDEX_NAME||' UNUSABLE;' from dba_indexes where owner='CDMDM' and index_type like '%BITMAP%' and table_name in ('W_CLAIMELMNT_D','W_EMPLOYEE_D','WC_UNIT_FINANCIAL_F','W_CLAIMELMNT_D_PZ','WC_MM_BILL_HEADER_F','W_EMPLOYEE_D_OLD');

Step 3 :

Move all indexes to new tablespace by below dynamic query :

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE TABLESPACE CDMDM_NEW compress ADVANCED high parallel 20;' from dba_indexes where owner='CDMDM' and tablespace_name='CDMDM' and index_type <> 'LOB';

Step 4 :

** Rebuild all Indexes are in UNUSABLE State :

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD parallel 20;' from dba_indexes where status='UNUSABLE' ;

Convert all indexes to noparallel

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' noparallel;' from dba_indexes where owner='CDMDM' and degree > 1;

Step 5 :

Compress all LOBS where securefile is YES

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' modify LOB ( '||COLUMN_NAME||' ) ( COMPRESS HIGH );' from dba_lobs where owner='CDMDM' and tablespace_name='CDMDM' and securefile='YES';

Step 6 :

Move all LOBS to new tablespace

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' move LOB ( '||COLUMN_NAME||' ) STORE AS ( tablespace CDMDM_NEW );' from dba_lobs where owner='CDMDM' and tablespace_name='CDMDM';

Move LOB Partitions :

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as (tablespace NONGWDATA1);' FROM DBA_LOB_PARTITIONS WHERE tablespace_name in ('NONGWDATA_NEW','NONGWDATA');

Move SDO LOB segments :

ALTER TABLE CCUSER."CC_USER" MOVE TABLESPACE OP VARRAY "SPATIALPOINTDENORM"."SDO_ELEM_INFO" STORE AS LOB(TABLESPACE LOB_NEW) VARRAY "SPATIALPOINTDENORM"."SDO_ORDINATES" STORE AS LOB(TABLESPACE LOB_NEW) parallel 16;

ALTER TABLE CCUSER."CC_ADDRESS" MOVE TABLESPACE OP VARRAY "SPATIALPOINT"."SDO_ELEM_INFO" STORE AS LOB(TABLESPACE LOB_NEW) VARRAY "SPATIALPOINT"."SDO_ORDINATES" STORE AS LOB(TABLESPACE LOB_NEW) parallel 16;

ALTER TABLE CCUSER."CC_CLAIM" MOVE TABLESPACE OP VARRAY "LOSSLOCATIONSPATIALDENORM"."SDO_ELEM_INFO" STORE AS LOB(TABLESPACE LOB_NEW) VARRAY "LOSSLOCATIONSPATIALDENORM"."SDO_ORDINATES" STORE AS LOB(TABLESPACE LOB_NEW) parallel 16;

Step 7 :

Validation :

select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='CDMDM';

select owner,object_name,object_type from dba_objects where status <> 'VALID' and owner='CDMDM';

select * from dba_segments where tablespace_name='CDMDM';

select * from dba_tables where tablespace_name='CDMDM';

select * from dba_indexes where tablespace_name='CDMDM';

select * from dba_lobs where tablespace_name='CDMDM';

select * from dba_tab_partitions where tablespace_name in ('ODSDATA2','ODS_BASE_NEW');

select * from dba_ind_partitions where tablespace_name in ('ODSDATA2','ODS_BASE_NEW');

select * from dba_lob_partitions where tablespace_name in ('ODSDATA2','ODS_BASE_NEW');

DATAFILE MOVEMENT Dynamic QUERY:

select 'alter database move datafile '''||FILE_NAME||''' to ''+DATAC1'';' from dba_data_files where file_name like '%SPARSE%';

Verify Default attribute tablespace:

select * from dba_part_tables where DEF_TABLESPACE_NAME like '%del%'

SELECT 'alter table ' || OWNER || '.' || TABLE_NAME || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE NONGWDATA1;' from dba_part_tables where DEF_TABLESPACE_NAME ='%del%'

Table Online Redefination :

exec DBMS_REDEFINITION.REDEF_TABLE(uname=>'CDH_GWODS',tname=>'CDH_BATCH_CTRLID',table_part_tablespace=>'CDH');

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

col USERNAME for a30

col TABLESPACE_NAME for a40

SELECT USERNAME, TABLESPACE_NAME, BYTES, MAX_BYTES FROM DBA_TS_QUOTAS ORDER BY USERNAME, TABLESPACE_NAME;

alter database datafile '/u02/oracle/oradata/u7bcs1d7/tools_01.dbf' resize 26g autoextend on next 250m maxsize unlimited;

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

ALTER TABLE MEDIATION.QRTZ_JOB_DETAILS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.QRTZ_TRIGGERS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.QRTZ_BLOB_TRIGGERS move LOB ( BLOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.QRTZ_CALENDARS move LOB ( CALENDAR ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.REST_TEMPLATE move LOB ( TEMPLATE ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.EVENT_HISTORY_DATA move LOB ( DETAILS ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.NOTIFICATIONS move LOB ( NOTIFICATION ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.NOTIFICATIONHISTORY move LOB ( NOTIFICATION ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.SOAPHEADER move LOB ( HEADER ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.CREATE$JAVA$LOB$TABLE move LOB ( LOB ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPADMIN move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPGWA move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPSCHEDULE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPCONFIG move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPDATAPATH move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPPOLICY move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPTEMPLATE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.TASKPAYLOAD move LOB ( TASK ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.JMSCONTEXT move LOB ( CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPSCHEDULE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPTEMPLATE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPADMIN move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPGWA move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.SOAPHEADER move LOB ( HEADER ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.JMSCONTEXT move LOB ( CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.TOAD_PLAN_TABLE move LOB ( OTHER_XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_BLOB_TRIGGERS move LOB ( BLOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_CALENDARS move LOB ( CALENDAR ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_JOB_DETAILS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_TRIGGERS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.XMLRESPONSE move LOB ( RESPONSEXML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.SYS_IMPORT_FULL_01 move LOB ( XML_CLOB ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.SYS_IMPORT_FULL_01 move LOB ( TARGET_XML_CLOB ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.BATCH_STEP_EXECUTION_CONTEXT move LOB ( SERIALIZED_CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.BATCH_JOB_EXECUTION_CONTEXT move LOB ( SERIALIZED_CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.CREATE$JAVA$LOB$TABLE move LOB ( LOB ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPCONFIG move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPDATAPATH move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPPOLICY move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.JMSTEST move LOB ( CONTEXT ) STORE AS ( tablespace DATA );

When moving a LOB column, if the LOB is stored as a SecureFile, and you want to maintain or explicitly specify that the LOB should continue to be stored as a SecureFile, you should include the SECUREFILE keyword in your ALTER TABLE statement.

Checking if a LOB is SecureFile or BasicFile

To check whether a LOB column is stored as SecureFile or BasicFile, you can query the DBA_LOBS view:

sql
SELECT TABLE_NAME, COLUMN_NAME, SECUREFILE
FROM DBA_LOBS
WHERE TABLE_NAME = 'your_table_name' AND COLUMN_NAME = 'your_column_name';

SECUREFILE: This column will show YES if the LOB is stored as a SecureFile, and NO if it's stored as a BasicFile.

Moving a SecureFile LOB

If the LOB is a SecureFile, and you want to maintain that storage type while moving it, you should use the following syntax:

sql
ALTER TABLE schema_name.table_name
MOVE LOB (column_name)
STORE AS SECUREFILE (TABLESPACE tablespace_name);

Example

Here is how you would move a SecureFile LOB column while ensuring it stays a SecureFile:

sql
ALTER TABLE MEDIATION.QRTZ_JOB_DETAILS 
MOVE LOB (JOB_DATA) 
STORE AS SECUREFILE (TABLESPACE DATA);

If you omit SECUREFILE, the column might revert to the BasicFile format depending on your Oracle database's default LOB storage settings.

Best Practice

For SecureFile LOBs: Always specify STORE AS SECUREFILE to ensure the LOB remains in the SecureFile format after the move.
For BasicFile LOBs: You don't need to specify anything special; just using the STORE AS (TABLESPACE tablespace_name) syntax is sufficient.

If you want to check the existing storage type before moving, always query DBA_LOBS to avoid inadvertently changing the LOB type.

SELECT OWNER, TABLE_NAME, COLUMN_NAME

FROM ALL_TAB_COLUMNS

WHERE DATA_TYPE = 'SDO_GEOMETRY';

DATA_TYPE

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

DATE

CLOB

CHAR

TIMESTAMP(6)

BLOB

RAW

NUMBER

UNDEFINED

VARCHAR2

LONG

TIMESTAMP(3)

NVARCHAR2

Tuesday, July 9, 2024

RAC

Oracle RAC 12cR1 introduced a new architecture option called Flex Clusters.

blacklist {
devnode "^(ram|raw|loop|fd|md|dm-|sr|scd|st|sdao)[0-9]*"
devnode "^asm/*"
devnode "^ofsctl"
wwid 3600605b0033286b0162b06912bb7c0dd
wwid 3600605b0033286b0162b06912bb7181b
wwid 3600605b0033286b0162b06912bb76c7b
}

[root@nmsis2d11 oracleasm]# cat /etc/multipath.conf | grep -A19 defaults

defaults {

polling_interval 5

path_grouping_policy multibus

failback immediate

user_friendly_names yes

max_fds 8192

}

blacklist {

devnode "^(ram|raw|loop|fd|md|dm-|sr|scd|st|sdao)[0-9]*"

devnode "^asm/*"

devnode "^ofsctl"

wwid 3600605b0033286b0162b06912bb7c0dd

wwid 3600605b0033286b0162b06912bb7181b

wwid 3600605b0033286b0162b06912bb76c7b

}

[root@ipagt1d5 ~]# cat /etc/multipath.conf | grep -A19 defaults

defaults {

user_friendly_names yes

failback immediate

prio const

find_multipaths yes

detect_prio yes

polling_interval 10

}

blacklist {

devnode "^(ram|raw|loop|fd|md|dm-|sr|scd|st)[0-9]*"

devnode "^asm/*"

devnode "^ofsctl"

}

[root@nmsis2d11 oragrid_s2d11_vg]# df -h /dev/mapper/t2ls2idb_s2d11_vg-t2ls2idb

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/t2ls2idb_s2d11_vg-t2ls2idb 99G 60G 34G 64% /opt/app/t2ls2idb

[root@nmsis2d11 oragrid_s2d11_vg]# df -h /dev/mapper/oragrid_s2d11_vg-oragrid

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/oragrid_s2d11_vg-oragrid 99G 56G 38G 60% /opt/app/oragrid

[root@nmsis2d11 oragrid_s2d11_vg]# ls -ltr /dev/mapper/

total 0

crw------- 1 root root 10, 236 Jul 3 14:34 control

lrwxrwxrwx 1 root root 8 Jul 15 02:28 oragrid_s2d11_vg-oragrid -> ../dm-24

lrwxrwxrwx 1 root root 8 Jul 15 02:28 t2ls2idb_s2d11_vg-t2ls2idb -> ../dm-25

[root@nmsis2d11 oragrid_s2d11_vg]# pwd

/dev/oragrid_s2d11_vg

[root@nmsis2d11 oragrid_s2d11_vg]# ls -ltr

total 0

lrwxrwxrwx 1 root root 8 Jul 15 02:28 oragrid -> ../dm-24

[root@nmsis2d11 t2ls2idb_s2d11_vg]# pwd

/dev/t2ls2idb_s2d11_vg

[root@nmsis2d11 t2ls2idb_s2d11_vg]# ls -ltr

total 0

lrwxrwxrwx 1 root root 8 Jul 15 02:28 t2ls2idb -> ../dm-25

[root@nmsis2d11 mapper]# ls -l

total 0

lrwxrwxrwx 1 root root 8 Jul 15 15:29 ASM_20171220_4.0G_EMC_1090_01A3 -> ../dm-22

lrwxrwxrwx 1 root root 7 Jul 15 15:29 ASM_20171220_4.0G_EMC_1090_01A4 -> ../dm-1

lrwxrwxrwx 1 root root 7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A5 -> ../dm-2

lrwxrwxrwx 1 root root 7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A6 -> ../dm-3

lrwxrwxrwx 1 root root 7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A7 -> ../dm-4

lrwxrwxrwx 1 root root 7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A8 -> ../dm-5

lrwxrwxrwx 1 root root 7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01A9 -> ../dm-6

lrwxrwxrwx 1 root root 7 Jul 15 15:29 ASM_20171220_50G_EMC_1090_01AA -> ../dm-7

lrwxrwxrwx 1 root root 7 Jul 15 15:29 ASM_20171220_725G_EMC_1090_019B -> ../dm-0

lrwxrwxrwx 1 root root 8 Jul 15 15:29 ASM_20171220_725G_EMC_1090_019C -> ../dm-11

lrwxrwxrwx 1 root root 8 Jul 15 16:27 ASM_20171220_725G_EMC_1090_019D -> ../dm-17

lrwxrwxrwx 1 root root 8 Jul 15 16:27 ASM_20171220_725G_EMC_1090_019E -> ../dm-18

lrwxrwxrwx 1 root root 8 Jul 15 16:00 ASM_20171220_725G_EMC_1090_019F -> ../dm-19

lrwxrwxrwx 1 root root 8 Jul 15 16:00 ASM_20171220_725G_EMC_1090_01A0 -> ../dm-23

lrwxrwxrwx 1 root root 8 Jul 15 16:25 ASM_20171220_725G_EMC_1090_01A1 -> ../dm-20

lrwxrwxrwx 1 root root 8 Jul 15 15:29 ASM_20171220_725G_EMC_1090_01A2 -> ../dm-21

lrwxrwxrwx 1 root root 8 Jul 15 16:00 ASM_20200311_725G_EMC_1090_0517 -> ../dm-10

lrwxrwxrwx 1 root root 8 Jul 15 16:15 ASM_20200311_725G_EMC_1090_0518 -> ../dm-12

lrwxrwxrwx 1 root root 8 Jul 15 16:00 ASM_20200311_725G_EMC_1090_0519 -> ../dm-13

lrwxrwxrwx 1 root root 8 Jul 15 15:29 ASM_20200311_725G_EMC_1090_051A -> ../dm-14

lrwxrwxrwx 1 root root 8 Jul 15 15:29 ASM_20200311_725G_EMC_1090_051B -> ../dm-15

lrwxrwxrwx 1 root root 8 Jul 15 15:29 ASM_20200311_725G_EMC_1090_051C -> ../dm-16

crw------- 1 root root 10, 236 Jul 3 14:34 control

lrwxrwxrwx 1 root root 7 Jul 15 15:29 LOCAL_20171220_100G_EMC_1090_01AB -> ../dm-8

lrwxrwxrwx 1 root root 7 Jul 15 15:29 LOCAL_20171220_100G_EMC_1090_01AC -> ../dm-9

lrwxrwxrwx 1 root root 8 Jul 15 02:28 oragrid_s2d11_vg-oragrid -> ../dm-24

lrwxrwxrwx 1 root root 8 Jul 15 02:28 t2ls2idb_s2d11_vg-t2ls2idb -> ../dm-25

[root@nmsis2d11 mapper]# df -Th

Filesystem Type Size Used Avail Use% Mounted on

devtmpfs devtmpfs 36G 0 36G 0% /dev

tmpfs tmpfs 36G 389M 35G 2% /dev/shm

tmpfs tmpfs 36G 234M 36G 1% /run

tmpfs tmpfs 36G 0 36G 0% /sys/fs/cgroup

/dev/sda7 ext4 19G 8.0G 9.3G 47% /

/dev/mapper/t2ls2idb_s2d11_vg-t2ls2idb ext4 99G 60G 34G 64% /opt/app/t2ls2idb

/dev/sda1 ext4 976M 276M 634M 31% /boot

/dev/sda6 ext4 4.8G 13M 4.6G 1% /tmp

/dev/sda5 ext4 15G 2.3G 12G 17% /var

/dev/sda2 ext4 15G 1.7G 13G 12% /home

/dev/sdd1 ext4 69G 202M 65G 1% /var/crash

/dev/mapper/oragrid_s2d11_vg-oragrid ext4 99G 56G 38G 60% /opt/app/oragrid

dy-tdevnas1:/dbbkups01 nfs 4.0T 3.7T 402G 91% /pac/prod_backup2

tmpfs tmpfs 7.1G 0 7.1G 0% /run/user/51012

tmpfs tmpfs 7.1G 0 7.1G 0% /run/user/0

tmpfs tmpfs 7.1G 0 7.1G 0% /run/user/1563708

dy-tdevnas1.snt.bst.bls.com:/osddba nfs 200G 184G 17G 92% /nas/osddba

dy-tdevnas1.snt.bst.bls.com:/osd nfs 2.0T 967G 1.1T 48% /nas/osd

tmpfs tmpfs 7.1G 0 7.1G 0% /run/user/21295

dy-tdevnas1:/dbbkups01 /pac/prod_backup2 nfs rw,bg,tcp,nfsvers=3,suid,noac,hard,rsize=32768,wsize=32768 0 0

ORA-01103: database name 'IPAGI1DB' in control file is not 'IPAGIT1D'

https://www.pythian.com/blog/technical-track/oracle-rman-restore-to-the-same-machine-as-the-original-database --> restore database from rman backup with in the same host

ACTION!="add|change", GOTO="dm_end"

ENV{DM_UDEV_RULES_VSN}!="?*", GOTO="dm_end"

# Set permissions for a DM device name starting with ASM for Oracle RAC

ENV{DM_NAME}=="ASM*", OWNER:="oragrid", GROUP:="asmadmin", MODE:="0660"

LABEL="dm_end"

[root@nmsis6d11 rules.d]# ls -l

total 8

-rw-r----- 1 root root 190 Oct 1 2023 55-usm.rules

-rw-r--r-- 1 root root 144 Jun 24 15:23 99-oracle.rules

[root@nmsis6d11 rules.d]# more 99-oracle.rules

# Set permissions for a DM device name starting with ASM for Oracle RAC

ENV{DM_NAME}=="ASM*", OWNER:="oragrid", GROUP:="asmadmin", MODE:="0660"

[root@nmsis6d11 rules.d]#

[root@ipagt5d12 rules.d]# more 99-oracle.rules

ACTION!="add|change", GOTO="dm_end"

ENV{DM_UDEV_RULES_VSN}!="?*", GOTO="dm_end"

# Set permissions for a DM device name starting with ASM for Oracle RAC

ENV{DM_NAME}=="ASM*", OWNER:="oragrid", GROUP:="asmadmin", MODE:="0660"

LABEL="dm_end"

-- Step 1: Check free space in datafiles

SELECT FILE_NAME, BYTES, BLOCKS, FREE_SPACE

FROM DBA_DATA_FILES df

JOIN (SELECT FILE_ID, SUM(BYTES) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY FILE_ID) fs

ON df.FILE_ID = fs.FILE_ID

WHERE df.TABLESPACE_NAME = 'your_tablespace_name';

-- Step 2: Move segments (tables and indexes) to free up space

ALTER TABLE your_table_name MOVE;

ALTER INDEX your_index_name REBUILD;

-- Step 3: Resize datafiles

ALTER DATABASE DATAFILE 'your_datafile_name1' RESIZE 500M;

ALTER DATABASE DATAFILE 'your_datafile_name2' RESIZE 300M;

-- Repeat for all datafiles

-- Step 4: Coalesce free space

ALTER TABLESPACE your_tablespace_name COALESCE;

select sum(bytes)/1024/1024/1024 from dba_segments where TABLESPACE_NAME='INDICES';

select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='TOOLS';

DECLARE

v_id NUMBER := 1;

v_clob CLOB;

v_name VARCHAR2(50) := 'Test Data';

v_date DATE := SYSDATE;

v_chunk CLOB := RPAD('X', 1000, 'X'); -- 1KB of data

v_clob_size NUMBER := 10485760; -- 10MB per row

PROCEDURE insert_row(p_id IN NUMBER, p_name IN VARCHAR2, p_date IN DATE, p_clob IN CLOB) IS

BEGIN

INSERT INTO test_table (id, name, created_date, data)

VALUES (p_id, p_name, p_date, p_clob);

END;

BEGIN

FOR i IN 1..1024 LOOP -- 1024 * 10MB = 10GB

DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

DBMS_LOB.OPEN(v_clob, DBMS_LOB.LOB_READWRITE);

FOR j IN 1..(v_clob_size / 1000) LOOP

DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_chunk), v_chunk);

END LOOP;

insert_row(v_id, v_name, v_date, v_clob);

v_id := v_id + 1;

DBMS_LOB.FREETEMPORARY(v_clob);

END LOOP;

COMMIT;

END;

From the provided information we see that DATA tablespace is having around 500 GB of free space, but you could not able to shrink all the free space from datafiles.
This is due to fragmentation in the datafiles. There could be an active datablock occupied by the objects data at the end of the datafile which is causing not to shrink the datafile.

To overcome this you need reorg all the objects in the tablespace to be able to shrink more space in the datafiles.

Please find below documents on how to move objects in the tablespace:

How to Move Different Database Objects to Another Tablespace ( Doc ID 1915040.1 )

0----

Do i need to reorg all the objects first , then go for shrinking the datafiles right?

moving within the same tablespace will do the job right?

no need to create new tablespace and then move into that newone --> ?

-------

Yes you need to reorg all the objects first and go for shrinking the datafiles.

No need to create the new tablespace as there is enough free space in the DATA tablespace. Moving within the same tablespace will do the job.

--------

t1ls1jdb@nmsjs1d1(1110) t1ls1jd1 /opt/app/t1ls1jdb/oracle$ more /home/oracle/oraadmin/rman_backup.sh

#!/bin/ksh

# ******************************************************************

# * NOTICE *

# *** NOT FOR USE OR DISCLOSURE OUTSIDE THE AT&T COMPANIES ****

# ******************************************************************

# *

# * Module name : rman_backup.sh

# *

# * Module description: Backup the database using RMAN.

# *

# * Date created :

# *

# * Author :

# *

# * Modification History : Pratul Malhotra

# * --------------------------------------------------------------

# *

# *******************************************************************

#set -x

# Format Log File for Backups

format_backup_logfile_name()

{

# Create Backup Log Directory if it does not Exist

if [ ! -d ${BACKUPSLOG_DIR} ]

then

mkdir -p ${BACKUPSLOG_DIR}

if [ ${ARCHIVEONLY} = Y ]

then

LOGFILE=${BACKUPSLOG_DIR}/DB_Arch_Backup_`date +%Y%m%d%H%M`.log

elif [ ${INCREMENTALONLY} = Y ]

then

LOGFILE=${BACKUPSLOG_DIR}/DB_Incremental_Backup_`date +%Y%m%d%H%M`.log

else

LOGFILE=${BACKUPSLOG_DIR}/DB_Full_Backup_`date +%Y%m%d%H%M`.log

}

# List Contents of Backup Directory

list_bkup_dir()

{

BkupDir=${1}

if [ -d ${BkupDir} ]

then

log "Listing ${BkupDir}"

ls -lisart ${BkupDir} | tee -a ${LOGFILE}

}

# Check for Hanging Backup

check_for_hanging_backup()

{

Oracle_Sid=${1}

Start_Date=${2}

# Create Lock File for this backup to help determine if backup is hanging

LockFile=/tmp/backup.${Oracle_Sid}.${Start_Date}.lck

touch ${LockFile}

BkupTimeLimit=`get_dbmodes_value ${Oracle_Sid} BKUPTIMELIMIT 60`

log "ORACLE_SID = ${Oracle_Sid} : BKUPTIMELIMIT = ${BkupTimeLimit}"

# Start background process to sleep for $BKUPTIMELIMIT minutes and then verify

# the backup for this SID has completed using the lock file created above

# (if the lock file still exists after sleeping, then the backup must have hung)

${ADMIN_DIR}/check_backup_status.sh ${Oracle_Sid} ${BkupTimeLimit} ${LockFile} &

}

# Cleanup after the Backup Ends

end_backup()

{

Oracle_Sid=${1}

Start_Date=${2}

# Remove Lock File created to determine if backup is hanging

LockFile=/tmp/backup.${Oracle_Sid}.${Start_Date}.lck

rm -f ${LockFile}

}

# Backup the Online Redo Logs for Cold Backups

bkup_redo_logs()

{

Oracle_Sid=${1}

BkupDir=${2}

log "Backing up online redo logs"

RedoLogsDir=`get_dbmodes_value ${Oracle_Sid} REDOLOGSDIR N`

RedoLogDirList=`echo ${RedoLogsDir} | sed "s/,/ /g"`

for Field in ${RedoLogDirList}

for RedoLog in `ls ${Field}/*redo*`

RedoTarget=${BkupDir}/`basename ${RedoLog}`.`date +%Y%m%d%H%M`

cp ${RedoLog} ${RedoTarget} | tee -a ${LOGFILE}

compress ${RedoTarget} | tee -a ${LOGFILE}

done

}

# Disable Previous Backups

disable_backups()

{

log "Disabling previous backups"

${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' @${SQL_DIR}/disable_backups.sql | tee -a ${LOGFILE}

rm -f /tmp/disable_backups.lst

rm -f /tmp/disable_backups.sql

}

# Backup the Database to Tape

bkup_db_to_tape()

{

BACKUP_POLICY=$1

NB_ORACLE_SERVER=`grep SERVER /usr/openv/netbackup/bp.conf | grep -v _SERVER | cut -d "=" -f2 | tr -d " "`

# BKRETENT is the number of days to keep backups.

# If BKRETENT is not set in dbmodes.txt then set the default REDUNDANCY to 31.

BKRETENT=`get_dbmodes_value ${ORACLE_SID} TAPERETENT 31`

log "ORACLE_SID = ${ORACLE_SID} : Backup Retention = ${BKRETENT} days"

TAG_DATE_FORMAT=%Y_%m_%d_%H%M

TAG_DATE=`date +${TAG_DATE_FORMAT}`

log "ORACLE_SID = ${ORACLE_SID} : Copying the database backup sets to tape using Veritas NetBackkup"

unset NLS_DATE_FORMAT

# export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

${ORACLE_HOME}/bin/rman target / catalog ${RMANDB_USERNAME}/${RMANDB_PASSWORD}@rmancatc.snt.bst.bls.com << EOF >> ${LOGFILE}

CONFIGURE RETENTION POLICY TO NONE;

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

CONFIGURE DEVICE TYPE SBT_TAPE BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE BACKUP OPTIMIZATION ON;

run {

allocate channel 't1' type 'SBT_TAPE' PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},

NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

allocate channel 't2' type 'SBT_TAPE' PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},

NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

allocate channel 't3' type 'SBT_TAPE' PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},

NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

allocate channel 't4' type 'SBT_TAPE' PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},

NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

backup backupset all;

release channel t1;

release channel t2;

release channel t3;

release channel t4;

}

allocate channel for maintenance device type 'SBT_TAPE'

PARMS='ENV=(NB_ORA_SERV=${NB_ORACLE_SERVER},NB_ORA_CLIENT=$HOST,NB_ORA_POLICY=${BACKUP_POLICY},NB_ORA_SCHED=Default-Application-Backup)';

DELETE NOPROMPT FORCE OBSOLETE RECOVERY WINDOW OF 31 DAYS;

release channel ;

# report obsolete ;

# delete force noprompt obsolete device type 'SBT_TAPE' ;

# crosscheck backup device type 'SBT_TAPE' ;

# delete force noprompt expired backup device type 'SBT_TAPE' ;

# list backup summary device type disk ;

list backup summary device type 'SBT_TAPE' ;

exit;

EOF

Status=${?}

return ${Status}

}

# Get Backup Directories

get_bkup_dirs()

{

case `hostname` in

nmsjs6d1|nmsjs6d2|nmsjs6d3|nmsjs6d4)

BACKUPDIR=+JBBOS_RECOVERY

BACKUPDIR1=+JBBOS_RECOVERY

BACKUPDIR2=+JBBOS_RECOVERY

BACKUPDIR3=+JBBOS_RECOVERY

BACKUPDIR4=+JBBOS_RECOVERY

;;

n5004002|n5004005|g0212021)

BACKUPDIR=/orabackup1/dumpdata

BACKUPDIR1=/orabackup1/dumpdata

BACKUPDIR2=/orabackup2/dumpdata

BACKUPDIR3=/orabackup3/dumpdata

BACKUPDIR4=/orabackup4/dumpdata

;;

sogbuat1)

BACKUPDIR=/orabackup01/dumpdata

BACKUPDIR1=/orabackup01/dumpdata

BACKUPDIR2=/orabackup02/dumpdata

BACKUPDIR3=/orabackup03/dumpdata

BACKUPDIR4=/orabackup04/dumpdata

;;

optiersb)

BACKUPDIR=/pac/optier/bkup/orabkup01/dumpdata01

BACKUPDIR1=/pac/optier/bkup/orabkup01/dumpdata01

BACKUPDIR2=/pac/optier/bkup/orabkup01/dumpdata01

BACKUPDIR3=/pac/optier/bkup/orabkup01/dumpdata01

BACKUPDIR4=/pac/optier/bkup/orabkup01/dumpdata01

;;

ads00620|ads00621|ads00644|ads00645|ads00616|ads00617|ads00648|ads00649|nmsf1n1|nmsf1n2|nmsf1n3|nmsf1n4|nmsf1n5|nmsf1n6|nmsf1n7|nmsf1n8|nmsp2n1|nmsp2n2|nmsp2n3|nm

sp2n4)

BACKUPDIR=/orabackup01/dumpdata

BACKUPDIR1=${BACKUPDIR}

BACKUPDIR2=${BACKUPDIR}

BACKUPDIR3=${BACKUPDIR}

BACKUPDIR4=${BACKUPDIR}

;;

ads00641|ads00642|lstst06|lstst11|nrsb_fs5|nrsb_fs6|ads00610|ads00609)

BACKUPDIR=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata01

BACKUPDIR1=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata01

BACKUPDIR2=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata02

BACKUPDIR3=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata03

BACKUPDIR4=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata04

;;

bcsldt10)

BACKUPDIR=/pac/${ORACLE_SID}/bkup/orabkup01/dumpdata01

BACKUPDIR1=${BACKUPDIR}

BACKUPDIR2=${BACKUPDIR}

BACKUPDIR3=${BACKUPDIR}

BACKUPDIR4=${BACKUPDIR}

;;

# BACKUPDIR=/pac/cam/bkup/orabkup01/dumpdata01

BACKUPDIR=`get_Backup_Dir ${ORACLE_SID}`

BACKUPDIR1=${BACKUPDIR}

BACKUPDIR2=${BACKUPDIR}

BACKUPDIR3=${BACKUPDIR}

BACKUPDIR4=${BACKUPDIR}

;;

esac

}

# Backup the Database

bkup_db_to_disk()

{

# BKRETENT is the number of days to keep backups.

# If BKRETENT is not set in dbmodes.txt then set the default REDUNDANCY to 5.

BKRETENT=`get_dbmodes_value ${ORACLE_SID} BKRETENT 5`

log "ORACLE_SID = ${ORACLE_SID} : Backup Retention = ${BKRETENT} days"

SPFILEFLAG=`get_spfile_flag`

log "ORACLE_SID = ${ORACLE_SID} : spfileflag=${SPFILEFLAG}"

TAG_DATE_FORMAT=%Y_%m_%d_%H%M

TAG_DATE=`date +${TAG_DATE_FORMAT}`

log "ORACLE_SID = ${ORACLE_SID} : Backing up the database with RMAN"

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

${ORACLE_HOME}/bin/rman target / catalog ${RMANDB_USERNAME}/${RMANDB_PASSWORD}@rmancatc.snt.bst.bls.com << EOF >> ${LOGFILE}

# Note: You cannot assign a tag to a backup of the current control file.

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUPDIR}/cf_%F';

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF ${BKRETENT} DAYS;

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE BACKUP OPTIMIZATION OFF;

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' CLEAR;

run {

crosscheck backup device type disk ;

crosscheck archivelog all;

crosscheck backup of archivelog all spfile;

delete force noprompt obsolete device type disk;

delete force noprompt expired backup device type disk;

delete force noprompt expired archivelog all device type disk;

delete force noprompt backupset device type disk completed before 'sysdate-${BKRETENT}';

# CR Copy ALTER between blocks

# Oracle auto does a LOG SWITCH as part of BACKUP block

sql 'alter system switch logfile';

sql 'alter system archive log current';

allocate channel c1 device type disk format='${BACKUPDIR1}/${TAG_DATE}_dbf_s%s_p%p';

allocate channel c2 device type disk format='${BACKUPDIR2}/${TAG_DATE}_dbf_s%s_p%p';

allocate channel c3 device type disk format='${BACKUPDIR3}/${TAG_DATE}_dbf_s%s_p%p';

allocate channel c4 device type disk format='${BACKUPDIR4}/${TAG_DATE}_dbf_s%s_p%p';

# allocate channel c1 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

# allocate channel c2 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

# allocate channel c3 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

# allocate channel c4 device type disk format='/pac/cam/bkup/orabkup01/dumpdata01/${TAG_DATE}_dbf_s%s_p%p';

backup

incremental level 0

filesperset 4

# format '${BACKUPDIR}/${TAG_DATE}_dbf_s%s_p%p'

database

tag = '${TAG_DATE}_bkup_db'

include current controlfile for standby;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

exit;

# ${SPFILEFLAG} ;

# exit;

# list backup summary device type disk ;

EOF

Status=${?}

return ${Status}

}

# Incremental Level 1 Backup of the Database (Below Logic is appended by Pankaj)

incremental_bkup_db_to_disk()