How to disable/setup autostart parameters for specified instance ?

Q: We have a 4-node RAC. I need to disable autostart of the DB on one node only.
   How to do it and how to see autostart parameters, confirming that specified instance was disabled?
   I didn’t find this in the output of the command: crsctl status res –t.

A:
You're right, crsctl status res –t don't show the instance autostart parameter.
The crsctl also don't show the disabled instance:
 

[root@exa1dbadm01 ~]# crsctl status res ora.orcl.db

NAME=ora.orcl.db
TYPE=ora.database.type
TARGET=ONLINE                  , ONLINE
STATE=ONLINE on exa1dbadm01, ONLINE on exa1dbadm02

[root@exa1dbadm01 ~]# crsctl status res ora.orcl.db -p
NAME=ora.orcl.db
TYPE=ora.database.type
ACL=owner:oracle:rwx,pgrp:oinstall:r--,other::r--,group:dba:r-x,group:racoper:r-x,user:grid:r-x
ACTIONS=relocate_client,group:"dba",group:"racoper",user:"grid",user:"oracle" status_target,group:"dba",group:"racoper",user:"grid",user:"oracle" listJavaServices,group:"dba",group:"racoper",user:"grid",user:"oracle" isPUJSDone,group:"dba",group:"racoper",user:"grid",user:"oracle" unmonitor,user:"grid" serviceDrain,group:"dba",group:"racoper",user:"grid",user:"oracle" monitor,user:"grid" startoption,group:"dba",group:"racoper",user:"grid",user:"oracle"
ACTION_SCRIPT=
ACTION_START_OPTION=
ACTION_TIMEOUT=600
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALLOW_RESTART=default
AUTO_START=restore
CARDINALITY=2
CHECK_INTERVAL=900
CHECK_TIMEOUT=30
CLEAN_TIMEOUT=60
CLUSTER_DATABASE=true
CSS_CRITICAL=no
DATABASE_TYPE=RAC
DB_UNIQUE_NAME=orcl
DEFAULT_NETNUM=
DELETE_TIMEOUT=60
DESCRIPTION=Oracle Database resource
DISABLED_REASON=
DRAIN_ID=
DRAIN_TIMEOUT=
ENABLED=1
ENABLED@SERVERNAME(exa1dbadm02)=0
FAILOVER_DELAY=0
FAILURE_INTERVAL=60
FAILURE_THRESHOLD=1
GEN_AUDIT_FILE_DEST=/u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/audit
GEN_RESTART=
GEN_RESTART@SERVERNAME(exa1dbadm01)=StartCompleted
GEN_RESTART@SERVERNAME(exa1dbadm02)=StartCompleted
GEN_START_OPTIONS=
GEN_USR_ORA_INST_NAME=
GEN_USR_ORA_INST_NAME@SERVERNAME(exa1dbadm01)=orcl1
GEN_USR_ORA_INST_NAME@SERVERNAME(exa1dbadm02)=orcl2
HOSTING_MEMBERS=
IGNORE_TARGET_ON_FAILURE=no
INSTANCE_FAILOVER=0
INTERMEDIATE_TIMEOUT=0
JAVA_SERVICES=
LOAD=1
LOGGING_LEVEL=1
MANAGEMENT_POLICY=AUTOMATIC
MODIFY_TIMEOUT=60
NLS_LANG=
OFFLINE_CHECK_INTERVAL=0
ONLINE_RELOCATION_TIMEOUT=0
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
ORACLE_HOME_OLD=
PATCH_IN_PROGRESS=false
PLACEMENT=restricted
PWFILE=+DATAC1/orcl/PASSWORD/pwdorcl.258.1149713235
RANK=0
RELOCATE_ACTION=0
RELOCATE_BY_DEPENDENCY=1
RELOCATE_KIND=offline
RESOURCE_GROUP=
RESTART_ATTEMPTS=2
RESTART_DELAY=0
ROLE=PRIMARY
SCRIPT_TIMEOUT=60
SERVER_CATEGORY=
SERVER_POOLS=ora.orcl
SERVER_POOLS_PQ=
SERVER_POOLS_RF=
SERVICE_NAMES=
SPFILE=+DATAC1/orcl/PARAMETERFILE/spfile.270.1149714021
START_CONCURRENCY=0
START_DEPENDENCIES=hard(global:uniform:ora.DATAC1.dg, global:uniform:ora.RECOC1.dg) pullup(global:ora.DATAC1.dg, global:ora.RECOC1.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns)
START_DEPENDENCIES_RTE_INTERNAL=<xml><Cond name="ASMClientMode">False</Cond><Cond name="ASMmode">remote</Cond><Arg name="dg" type="ResList">ora.DATAC1.dg,ora.RECOC1.dg</Arg><Arg name="acfs_or_nfs" type="ResList"></Arg><Cond name="OHResExist">False</Cond><Cond name="DATABASE_TYPE">RAC</Cond><Cond name="MANAGEMENT_POLICY">AUTOMATIC</Cond><Arg name="acfs_and_nfs" type="ResList"></Arg></xml>
START_DEPENDENCIES_TEMPLATE=<xml>hard(<If cond="ASMClientMode" op="neq" value="True"><Then><If cond="ASMmode" value="remote"><Then><ResList>uniform:global:{dg}</ResList>,</Then><Else><ResList>{dg}</ResList>,</Else></If></Then></If><ResList>{acfs_or_nfs}</ResList><If cond="OHResExist" value="True"><Then>,<Res>{oraclehome}</Res></Then></If>) weak(<If cond="DATABASE_TYPE" op="eql" value="RAC|RACOneNode"><Then>type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns</Then><Else>type:ora.listener.type,uniform:ora.ons</Else></If>) dispersion(<If cond="DATABASE_TYPE" value="RACOneNode"><Then><If cond="DB_CENTRIC" value="False"><Then>type:ora.database.type</Then></If></Then></If>) pullup(<If cond="ASMClientMode" op="neq" value="True"><Then><If cond="MANAGEMENT_POLICY" op="neql" value="MANUAL|NORESTART"><Then><If cond="ASMmode" value="remote"><Then><ResList>global:{dg}</ResList>,</Then><Else><ResList>{dg}</ResList>,</Else></If></Then></If></Then></If><ResList>{acfs_and_nfs}</ResList><If cond="OHResExist" value="True"><Then>,<Res>{oraclehome}</Res></Then></If>)</xml>
START_TIMEOUT=600
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(global:intermediate:ora.asm,global:shutdown:ora.DATAC1.dg,global:shutdown:ora.RECOC1.dg)
STOP_DEPENDENCIES_RTE_INTERNAL=<xml><Cond name="ASMClientMode">False</Cond><Cond name="DGExist">True</Cond><Cond name="ASMmode">remote</Cond><Arg name="dg" type="ResList">ora.DATAC1.dg,ora.RECOC1.dg</Arg><Arg name="acfs_same_as_oh" type="Res"></Arg><Arg name="acfs" type="ResList"></Arg><Cond name="OHResExist">False</Cond></xml>
STOP_DEPENDENCIES_TEMPLATE=<xml><If cond="ASMClientMode" op="neq" value="True"><Then><If cond="DGExist" value="True"><Then><If cond="ASMmode" value="remote"> <Then>hard(global:intermediate:ora.asm,<ResList>global:shutdown:{dg}</ResList>)</Then> <Else>hard(intermediate:ora.asm,<ResList>shutdown:{dg}</ResList>)</Else> </If></Then> </If>hard(<Res>{acfs_same_as_oh}</Res>,<ResList>intermediate:{acfs}</ResList>)</Then> <Else>hard(intermediate:<Res>{nfs}</Res>)</Else> </If><If cond="OHResExist" value="True"><Then>hard(intermediate: <Res>{oraclehome}</Res>)</Then></If></xml>
STOP_TIMEOUT=600
TARGET_DEFAULT=default
TARGET_NAME=
TARGET_TYPE=
TYPE_VERSION=3.3
UPTIME_THRESHOLD=1h
USER_WORKLOAD=yes
USE_QOS_DATA=no
USE_STICKINESS=0
USR_ORA_DB_NAME=orcl
USR_ORA_DOMAIN=
USR_ORA_ENV=
USR_ORA_FLAGS=
USR_ORA_INST_NAME=
USR_ORA_INST_NAME@SERVERNAME(exa1dbadm01)=orcl1
USR_ORA_INST_NAME@SERVERNAME(exa1dbadm02)=orcl2
USR_ORA_OPEN_MODE=open
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
WORKLOAD_CPU=0
WORKLOAD_CPU_CAP=0
WORKLOAD_MEMORY_MAX=0
WORKLOAD_MEMORY_TARGET=0

[root@exa1dbadm01 ~]# crsctl status res ora.orcl.db -p|grep orcl

NAME=ora.orcl.db
DB_UNIQUE_NAME=orcl
GEN_USR_ORA_INST_NAME@SERVERNAME(exa1dbadm01)=orcl1
GEN_USR_ORA_INST_NAME@SERVERNAME(exa1dbadm02)=orcl2
PWFILE=+DATAC1/orcl/PASSWORD/pwdorcl.258.1149713235
SERVER_POOLS=ora.orcl
USR_ORA_DB_NAME=orcl
USR_ORA_INST_NAME@SERVERNAME(exa1dbadm01)=orcl1
USR_ORA_INST_NAME@SERVERNAME(exa1dbadm02)=orcl2

[root@exa1dbadm01 ~]#


Q: Which command can be used to disable automatic start?

A:

To disable/enable autostart for whole DATABASE (all instances):
srvctl disable database -db $DBNAME
srvctl enable  database -db $DBNAME
Ins a such a way we disable autostart of the database on all nodes. All instances.

But how can we prevent autostart on one node only? Only for one instance ?

To disable/enable autostart for certain instance:
srvctl disable instance -db orcl –i orcl2
srvctl enable  instance -db orcl –i orcl2

Q: How to see autostart property for disabled instance in the cluster settings?
 

A:
 

Let experiment.

1st try as "grid" account.

Choose the appropriate DB.

I like use -v option is srvctl because it bring more information:

[grid@exa1dbadm01 ~]$ srvctl config db -v
orcl  /u01/app/oracle/product/19.0.0.0/dbhome_1       19.0.0.0.0
ok_awr  /u01/app/oracle/product/19.0.0.0/dbhome_1913    19.0.0.0.0


[grid@exa1dbadm01 ~]$ srvctl disable instance -d orcl -i orcl2
PRCR-1012 : Failed to disable resource ora.orcl.db
PRCR-1071 : Failed to register or update resource ora.orcl.db
CRS-0245:  User doesn't have enough privilege to perform the operation

The syntax is right, but "grid" account in OS doesn't have enough privileges. OK.

The rule to use srvctl: USE SRVCTL FROM ORACLE HOME WHERE DB IS STARTED. DON'T USE SRVCTL FROM GRID HOME OR OTHER ORACLE HOME.

[root@exa1dbadm01 ~]# su - oracle

[oracle@exa1dbadm01 ~]$ srvctl config db -v
orcl  /u01/app/oracle/product/19.0.0.0/dbhome_1       19.0.0.0.0
ok_awr  /u01/app/oracle/product/19.0.0.0/dbhome_1913    19.0.0.0.0

[oracle@exa1dbadm01 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl disable instance -d orcl -i orcl2

Previous command was completed successfully. And silently.
Let obtain the status (with -v, verbose):

[oracle@exa1dbadm01 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl  status instance -d orcl -i orcl1,orcl2 -v
Instance orcl1 is running on node exa1dbadm01 with online services srvorcl1. Instance status: Open.
Instance orcl2 is running on node exa1dbadm02. Instance status: Open.

The above output don't show the DISABLED.
May be my instance is not disabled? Let me disable it 2nd time:

[oracle@exa1dbadm01 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl disable instance -d orcl -i orcl2
PRCC-1013 : orcl was already disabled on exa1dbadm02
PRCR-1003 : Resource ora.orcl.db is already disabled

But "srvctl status instance" don't show that instance autostart property is disabled|enabled.

srvctl CONFIG - don't show the DISABLED instance too:

[oracle@exa1dbadm01 ~]$ srvctl config db -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATAC1/orcl/PARAMETERFILE/spfile.270.1149714021
Password file: +DATAC1/orcl/PASSWORD/pwdorcl.258.1149713235
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATAC1,RECOC1
Mount point paths:
Services: srvorcl1
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: racoper
Database instances: orcl1,orcl2
Configured nodes: exa1dbadm01,exa1dbadm02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

How to view autostart at instance level?

As you see the "-v" option don't show the DISABLED instance:
[oracle@exa1dbadm01 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl  status instance -d orcl -i orcl1,orcl2 -v
Instance orcl1 is running on node exa1dbadm01 with online services srvorcl1. Instance status: Open.
Instance orcl2 is running on node exa1dbadm02. Instance status: Open.

The only "-f" option gives the expected output:

[oracle@exa1dbadm01 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl  status instance -d orcl -i orcl1,orcl2 -f -v
Instance orcl1 is running on node exa1dbadm01 with online services srvorcl1. Instance status: Open.
Instance orcl2 is disabled on node exa1dbadm02
Instance orcl2 is running on node exa1dbadm02. Instance status: Open.

Bingo!

Don't forget to restore the instance autostart:
[oracle@exa1dbadm01 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl enable instance -d orcl -i orcl2

Как перезапустить ASM не останавливая БД

У заказчика RAC и потребовалось перезапустить один инстанс ASM (на одной ноде). И возникло желание перезапустить локальный инстанс ASM не останавливая локальные инстансы БД (ASM уже давно Flex, поэтому должно срабоать).

Прежде, чем делать это у заказчика я попробовал останавливать ASM на своём ноутбуке. RAC из 2-х нод.  Но как бы я ни останавливал ASM ("srvctl stop asm" или "SQL> shutdown immediate" или "asmcmd shutdown") - во всех трёх случаях локальный инстанс БД падал.

При этом установка обновлений на Grid в том же RAC на ноутбуке происходила без остановки локального инстанса БД, вот по этому образцу: https://dohdatabase.com/2023/03/10/how-to-patch-oracle-grid-infrastructure-19c-using-zero-downtime-oracle-grid-infrastructure-patching/
Т.е. во время обновления Grid локальный инстанс БД работал.

Ответ нашёлся в документации про Flex ASM: есть команда принудительно переключать клиента на другой инстанс ASM (после этого можно останавливать локальный ASM):

"The SRVCTL UPDATE INSTANCE and SRVCTL UPDATE IOSERVER commands can change the Oracle ASM instance for a database, or the Oracle IOServer instance for a database, or the Oracle ASM instance for an Oracle IOServer.

$ srvctl update instance -db my_orcl_db -instance my_instance_1 -targetinstance my_asm_instance_1
"

"You can use the ALTER SYSTEM RELOCATE CLIENT command to relocate a client. For example:

SQL> ALTER SYSTEM RELOCATE CLIENT 'client-id'; "

https://docs.oracle.com/en/database/oracle/oracle-database/19/ostmg/manage-flex-asm.html#GUID-39D13B70-30AE-4DFE-BFF9-2CD3A33A82E0

MAX_STRING_SIZE in primary-standby env

Для primary-standby  миграция на MAX_STRING_SIZE=EXTENDED происходит в следующем порядке :

Делаем миграцию на primary в MAX_STRING_SIZE=EXTENDED. Стендбай в этот период не трогаем, в нём еще MAX_STRING_SIZE=STANDARD, при этом журналы будут накатываться на стендбай сколь угодно долго. Дожидаемся того момента, чтобы журналы после миграции на primary на max_string_size='EXTENDED' накатились на стендбай (можно один, лучше несколько штук). Т.е. на стедбай должны доехать изменения словаря с primary.

После этого на стендбае выполняем
ALTER SYSTEM SET max_string_size='EXTENDED' SCOPE=SPFILE;
и перестартовываем стендбай. 

На этом миграция стендбая закончена.

Для проверки можно создать на primary табличку с varchar=32K и записать туда длинную строку, открыть стендбай в режиме ReadOnly, дождаться когда накатятся соответстующие журналы и убедиться, что длинные строки читаются на стендбае.

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

Начало в MAX_STRING_SIZE=EXTENDED, ORA-00910, ORA-14694
https://exadata-dba.blogspot.com/2022/12/maxstringsizeextended-ora-00910-ora.html

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

The migration of primary-standby env to MAX_STRING_SIZE=EXTENDED occurs in the following order: 

 

1) Migrate the primary to MAX_STRING_SIZE=EXTENDED.  

 

Do not touch the standby during this period. The log files will apply to the standby for as long as desired.

Apply some amount of archived logs in order to apply all max_string_size='EXTENDED' changes from primary to the standby's dictionary. 

 

After the standby's dictionary have applied the max_string_size='EXTENDED' changes from primary you could perform
SQL> ALTER SYSTEM SET max_string_size='EXTENDED' SCOPE=SPFILE; 

 and restart the standby. 

The migration of the standby completed!

 

To check, that standby is 32K you can create a table with varchar=32K on the primary and write a long string. Then open the standby in ReadOnly mode, wait for the corresponding logs to be applied and check if the standby is now 32K-aware.

Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64

1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BASICFILE CLOB, SECUREFILE BLOB and SECUREFILE CLOB.
2) Filled LOB columns with some amount of data and
3) Deleted the LOB-data with empty() function
4) Then obtain the space occupied by LOB-segments and
5) Try commands to free occupied LOB-space:
- ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (DEALLOCATE UNUSED [keep 0]);
- ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (SHRINK SPACE CASCADE);

The script below:
--------------------------------------
set lines 200
set pages 99

conn  / as sysdba
drop user test_lob cascade;
drop tablespace test_lob including contents and datafiles;

create tablespace test_lob extent management local segment space management auto datafile '/oradata/ORCL/test_lob.dbf' size 100m autoextend off;
                             
create user test_lob identified by test_lob default tablespace test_lob;
grant dba to test_lob;

conn test_lob/test_lob

create table lob_deallocate
   (    va varchar2(100 BYTE)
       ,BASICFILE_BLOB BLOB
       ,BASICFILE_CLOB CLOB
       ,SECUREFILE_BLOB BLOB
       ,SECUREFILE_CLOB CLOB
   )  
        TABLESPACE test_lob
  LOB (BASICFILE_BLOB)  STORE AS BASICFILE  BF_BLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
  LOB (BASICFILE_CLOB)  STORE AS BASICFILE  BF_CLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
  LOB (SECUREFILE_BLOB) STORE AS SECUREFILE SF_BLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
  LOB (SECUREFILE_CLOB) STORE AS SECUREFILE SF_CLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING ) ;

-- Fill the table with some data to blow up LOB-segments
declare
    l_data long := rpad('*',30000,'*');
begin
        for i in 1 .. 100
        loop
              insert into lob_deallocate values ( i,
              utl_raw.cast_to_raw(l_data),
              utl_raw.cast_to_raw(l_data),
              utl_raw.cast_to_raw(l_data),
              utl_raw.cast_to_raw(l_data) );
        end loop;
end;
/
commit;

-- Measure the LOB-segments
select  sum(dbms_lob.getlength(BASICFILE_BLOB))
       ,sum(dbms_lob.getlength(BASICFILE_CLOB))
       ,sum(dbms_lob.getlength(SECUREFILE_BLOB))
       ,sum(dbms_lob.getlength(SECUREFILE_CLOB))
from lob_deallocate;

col tablespace_name for a6
col segment_name for a28
select SEGMENT_NAME,SEGMENT_TYPE,SEGMENT_SUBTYPE, BYTES, BLOCKS,EXTENTS, INITIAL_EXTENT,NEXT_EXTENT from user_segments;
select SEGMENT_NAME,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where SEGMENT_TYPE='LOBSEGMENT';

select * from USER_LOBS;

-- Delete the LOB-data
update lob_deallocate set
          BASICFILE_BLOB=empty_blob(),
          BASICFILE_CLOB=empty_clob(),
          SECUREFILE_BLOB=empty_blob(),
          SECUREFILE_CLOB=empty_clob();
commit;

-- Measure the LOB-segments
select  sum(dbms_lob.getlength(BASICFILE_BLOB))
       ,sum(dbms_lob.getlength(BASICFILE_CLOB))
       ,sum(dbms_lob.getlength(SECUREFILE_BLOB))
       ,sum(dbms_lob.getlength(SECUREFILE_CLOB))
from lob_deallocate;

select SEGMENT_NAME,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where SEGMENT_TYPE='LOBSEGMENT';

ALTER TABLE lob_deallocate ENABLE ROW MOVEMENT;

-- try ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (DEALLOCATE UNUSED [keep 0]);
-- try ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (SHRINK SPACE CASCADE);

ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_BLOB)  (DEALLOCATE UNUSED keep 0);
ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_CLOB)  (DEALLOCATE UNUSED keep 0);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_BLOB) (DEALLOCATE UNUSED keep 0);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_CLOB) (DEALLOCATE UNUSED keep 0);

-- Measure the LOB-segments
select SEGMENT_NAME,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where SEGMENT_TYPE='LOBSEGMENT';


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

Tablespace TEST_LOB created.
User TEST_LOB created.
Grant succeeded.
Connected.
Table LOB_DEALLOCATE created.
PL/SQL procedure successfully completed.
Commit complete.

SUM(DBMS_LOB.GETLENGTH(BASICFILE_BLOB)) SUM(DBMS_LOB.GETLENGTH(BASICFILE_CLOB)) SUM(DBMS_LOB.GETLENGTH(SECUREFILE_BLOB)) SUM(DBMS_LOB.GETLENGTH(SECUREFILE_CLOB))
_______________________________________ _______________________________________ ________________________________________ ________________________________________
                                3000000                                 3276700                                  3000000                                  3276700


               SEGMENT_NAME    SEGMENT_TYPE    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
___________________________ _______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB                     LOBSEGMENT      ASSM                   4194304       512         19             65536        1048576
BF_CLOB                     LOBSEGMENT      ASSM                   8388608      1024         23             65536        1048576
LOB_DEALLOCATE              TABLE           ASSM                     65536         8          1             65536        1048576
SF_BLOB                     LOBSEGMENT      SECUREFILE             5439488       664          7            106496        1048576
SF_CLOB                     LOBSEGMENT      SECUREFILE            16973824      2072         11            106496        1048576
SYS_IL0000077702C00002$$    LOBINDEX        ASSM                     65536         8          1             65536        1048576
SYS_IL0000077702C00003$$    LOBINDEX        ASSM                     65536         8          1             65536        1048576
SYS_IL0000077702C00004$$    LOBINDEX        ASSM                     65536         8          1             65536        1048576
SYS_IL0000077702C00005$$    LOBINDEX        ASSM                     65536         8          1             65536        1048576

9 rows selected.


   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                   4194304       512         19             65536        1048576
BF_CLOB         ASSM                   8388608      1024         23             65536        1048576
SF_BLOB         SECUREFILE             5439488       664          7            106496        1048576
SF_CLOB         SECUREFILE            16973824      2072         11            106496        1048576


       TABLE_NAME        COLUMN_NAME    SEGMENT_NAME    TABLESPACE_NAME                  INDEX_NAME    CHUNK    PCTVERSION    RETENTION    FREEPOOLS    CACHE    LOGGING    ENCRYPT    COMPRESSION    DEDUPLICATION    IN_ROW             FORMAT    PARTITIONED    SECUREFILE    SEGMENT_CREATED    RETENTION_TYPE    RETENTION_VALUE
_________________ __________________ _______________ __________________ ___________________________ ________ _____________ ____________ ____________ ________ __________ __________ ______________ ________________ _________ __________________ ______________ _____________ __________________ _________________ __________________
LOB_DEALLOCATE    BASICFILE_BLOB     BF_BLOB         TEST_LOB           SYS_IL0000077702C00002$$        8192                        900              NO       YES        NONE       NONE           NONE             NO        NOT APPLICABLE     NO             NO            YES                YES                                  
LOB_DEALLOCATE    BASICFILE_CLOB     BF_CLOB         TEST_LOB           SYS_IL0000077702C00003$$        8192                        900              NO       YES        NONE       NONE           NONE             NO        ENDIAN NEUTRAL     NO             NO            YES                YES                                  
LOB_DEALLOCATE    SECUREFILE_BLOB    SF_BLOB         TEST_LOB           SYS_IL0000077702C00004$$        8192                                         NO       YES        NO         NO             NO               NO        NOT APPLICABLE     NO             YES           YES                DEFAULT                              
LOB_DEALLOCATE    SECUREFILE_CLOB    SF_CLOB         TEST_LOB           SYS_IL0000077702C00005$$        8192                                         NO       YES        NO         NO             NO               NO        ENDIAN NEUTRAL     NO             YES           YES                DEFAULT                              


100 rows updated.


Commit complete.


   SUM(DBMS_LOB.GETLENGTH(BASICFILE_BLOB))    SUM(DBMS_LOB.GETLENGTH(BASICFILE_CLOB))    SUM(DBMS_LOB.GETLENGTH(SECUREFILE_BLOB))    SUM(DBMS_LOB.GETLENGTH(SECUREFILE_CLOB))
__________________________________________ __________________________________________ ___________________________________________ ___________________________________________
                                         0                                          0                                           0                                           0

Before DEALLOCATE UNUSED:

   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                   4194304       512         19             65536        1048576
BF_CLOB         ASSM                   8388608      1024         23             65536        1048576
SF_BLOB         SECUREFILE             5439488       664          7            106496        1048576
SF_CLOB         SECUREFILE            16973824      2072         11            106496        1048576


Table LOB_DEALLOCATE altered.  -- row movement


Table LOB_DEALLOCATE altered.
Table LOB_DEALLOCATE altered.
Table LOB_DEALLOCATE altered.
Table LOB_DEALLOCATE altered.

After DEALLOCATE UNUSED:

   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                   4194304       512         19             65536        1048576
BF_CLOB         ASSM                   8388608      1024         23             65536        1048576
SF_BLOB         SECUREFILE             5439488       664          7            106496        1048576
SF_CLOB         SECUREFILE            16973824      2072         11            106496        1048576

As we see DEALLOCATE UNUSED doesn''t free the space occupied by LOBs.

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

The 2nd run is for SHRINK SPACE, i changed 4 lines in my script:

ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_BLOB)  (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_CLOB)  (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_BLOB) (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_CLOB) (SHRINK SPACE CASCADE);

Before SHRINK:
   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                   4194304       512         19             65536        1048576
BF_CLOB         ASSM                   8388608      1024         23             65536        1048576
SF_BLOB         SECUREFILE             5373952       656          6            106496        1048576
SF_CLOB         SECUREFILE            16908288      2064         10            106496        1048576


Table LOB_DEALLOCATE_UNUSED altered. -- row movement
Table LOB_DEALLOCATE_UNUSED altered.
Table LOB_DEALLOCATE_UNUSED altered.
Table LOB_DEALLOCATE_UNUSED altered.
Table LOB_DEALLOCATE_UNUSED altered.

After SHRINK:

   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                     65536         8          1             65536        1048576
BF_CLOB         ASSM                     65536         8          1             65536        1048576
SF_BLOB         SECUREFILE             5373952       656          6            106496        1048576
SF_CLOB         SECUREFILE            16908288      2064         10            106496        1048576

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


SHRINK works for BASICFILE only:

          | BLOB | CLOB |
-------------------------
BASICFILE | YES  | YES  |
SECUREFILE| NO   | NO   |
--------------------------

Exadata Scrubbing

 

1. Появление сбойных секторов на диске автоматически запускает дополнительный scrubbing:

8.2.4 Adaptive Scrubbing Schedule
In release 12.1.2.3.0, if a bad sector is found on a hard disk in a current scrubbing job, Oracle Exadata System Software will schedule a follow-up scrubbing job for that disk in one week. When no bad sectors are found in a scrubbing job for that disk, the schedule will fall back to the scrubbing schedule specified by the hardDiskScrubInterval attribute.If the user has changed the hardDiskScrubInterval to less than or equal to weekly, Oracle Exadata System Software will use the user-configured frequency instead of the weekly follow-up schedule even if bad sectors are found.
Exadata adaptively and automatically increase the frequency of scrubbing on that disk until all corruptions are repaired.

2. Scrubbing - не применяется к Flash-селлам

3. Работу по восстановлению избыточности выполняет ASM:  
If scrubbing detects a sector is corrupted, the storage server requests ASM to repair the sector from one of the mirrors on another storage server. This is reason why multiple ASM-mirrors are essential.
Получается, что если ASM остановлен, то Scrubbing не может исправить данные на дисках

4. Scrubbing is an automated process on Exadata that kicks in when the disks are idle ( less than 25% busy )

5. How do you see if scrubbing is in action?

CellCLI> list metriccurrent where name = 'CD_IO_BY_R_SCRUB_SEC' and metricObjectName like 'CD.*'

CD_IO_BY_R_SCRUB_SEC    CD_00_exadbm01celadm01  115 MB/sec  <<< scrubbing sectors at a rate of around 115MB/s.
CD_IO_BY_R_SCRUB_SEC    CD_01_exadbm01celadm01  118 MB/sec
CD_IO_BY_R_SCRUB_SEC    CD_02_exadbm01celadm01  117 MB/sec
     ...
The cell above represents an idle cell. If it were under load, the values on the right would drop to 0 MB/sec.

6. Hard disk drives in the High Capacity storage servers connect to a disk controller, which includes a 2G cache. AWR is reporting the number of IOPS serviced by the disk controller cache, not the physical IOPS serviced by the disk itself.

ORA-31603: object "SYS_NT#***" of type TABLE not found in schema"

Trying to get the DDL for a table we obtain the error:

SQL> select dbms_metadata.get_ddl('TABLE','SYS_NT#Y85_UAGY5$7BMV2SL63J7JM','&owner') from dual;

ERROR:
ORA-31603: object "SYS_NT#Y85_UAGY5$7BMV2SL63J7JM" of type TABLE not found in schema "&owner"
ORA-06512: at "SYS.DBMS_METADATA", line 6737
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6724
ORA-06512: at "SYS.DBMS_METADATA", line 9740
ORA-06512: at line 1

no rows selected
 

Is the table exists?

SQL> select OBJECT_NAME Name, OBJECT_TYPE, STATUS from dba_objects where owner='...' and OBJECT_NAME like '%SYS_%';


NAME                           OBJECT_TYPE STATUS
------------------------------ ----------- ----------
SYS_NT#Y85_UAGY5$7BMV2SL63J7JM TABLE       INVALID
 

Explanation: objects with name starting from SYS_NT# is "system generated nested tables ID"

Run the 2 queries, they show the PARENT_TABLE:

set lines 400

col TABLE_NAME          for a18
col TABLE_TYPE_OWNER    for a12
col TABLE_TYPE_NAME     for a24
col PARENT_TABLE_NAME   for a24
col PARENT_TABLE_COLUMN for a24

select TABLE_NAME,TABLE_TYPE_OWNER,TABLE_TYPE_NAME,PARENT_TABLE_NAME,PARENT_TABLE_COLUMN from dba_nested_tables where owner='&owner';

col owner        for a12
col nested_table for a24

select dio.owner,
     dio.object_name as nested_table,
     dio.status,
     nt.obj#,
     do.owner,
     do.object_name as parent_table
from dba_invalid_objects dio,
   ntab$ nt,
   dba_objects do
where dio.object_id=nt.ntab#
and nt.obj#=do.object_id
order by dio.object_id;

IPv6 is coming

Amazon will start charging for IPv4:

We are introducing a new charge for public IPv4 addresses. Effective February 1, 2024 there will be a charge of $0.005 per IP per hour for all public IPv4 addresses, whether attached to a service or not (there is already a charge for public IPv4 addresses you allocate in your account but don’t attach to an EC2 instance).

This change intended to encourage you to be a bit more frugal with your use of public IPv4 addresses and to think about accelerating your adoption of IPv6 as a modernization and conservation measure.. (https://aws.amazon.com/blogs/aws/new-aws-public-ipv4-address-charge-public-ip-insights/)

The black/blank/blind window in runInstaller, dbca, sqldeveloper

 After installing the Linux 8.9 with default settings i obtained the black/blank/blind (it is difficult to explain) window in runInstaller, dbca and sqldeveloper. No content, no buttons :

Oracle Linux 8.9 in VirtualBox on my laptop.

Obviously the problem in Java settings.

$ java -version
openjdk version "17.0.9" 2023-10-17 LTS
OpenJDK Runtime Environment (Red_Hat-17.0.9.0.9-2.0.1) (build 17.0.9+9-LTS)
OpenJDK 64-Bit Server VM (Red_Hat-17.0.9.0.9-2.0.1) (build 17.0.9+9-LTS, mixed mode, sharing)

 

SOLUTION is 

12.2 : ./runInstaller output results with blank screen (Doc ID 2294098.1)

 export _JAVA_OPTIONS='-Dsun.java2d.xrender=false'

The investigation opened that runInstaller and DBCA don't need the Java/JDK installed into OS. The runInstaller and DBCA use java from ORACLE_HOME. So i remove them but runInstaller and DBCA works well:

 

 

The SQLDeveloper needs the external JDK.

After install OpenJDK the SQLDeveloper will show content in the windows. You don't need the Oracle Java/JDK to run SQLDeveloper:

# yum install java-17-openjdk
...
# java -version
openjdk version "17.0.9" 2023-10-17 LTS
OpenJDK Runtime Environment (Red_Hat-17.0.9.0.9-2.0.1) (build 17.0.9+9-LTS)
OpenJDK 64-Bit Server VM (Red_Hat-17.0.9.0.9-2.0.1) (build 17.0.9+9-LTS, mixed mode, sharing)