How to list index partitions and their sizes

To list index partitions for index SALES_IDX1:

SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXPART.INDEX_NAME='SALES_IDX1'
AND    S.SEGMENT_TYPE='INDEX PARTITION'
-- To only list partitions in tablespace DATA1, uncomment the following line:
-- AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX1	SYS_P177667	DATA1	8
SH	SALES_IDX1	SYS_P177518	DATA1	5
SH	SALES_IDX1	SYS_P44844	DATA1	3
SH	SALES_IDX1	SYS_P44663	DATA1	2
SH	SALES_IDX1	SYS_P177677	DATA1	2

To list all index partitions for tablespace DATA1:

SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
AND    S.SEGMENT_TYPE='INDEX PARTITION'
AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	GB
SH	SALES_IDX1	SYS_P177667	8
SH	SALES_IDX1	SYS_P177518	5
SH	SALES_IDX1	SYS_P44844	3
SH	SALES_IDX1	SYS_P177677	2
SH	SALES_IDX1	SYS_P44663	2
SH	SALES_IDX2	SYS_P179608	1
SH	SALES_IDX2	SYS_P178334	1
SH	SALEX_IDX2	SYS_P178459	1
SH	SALES_IDX3	SYS_P28534	0
SH	SALES_IDX3	SYS_P50905	0

To list the names of all partitioned indexes and their total sizes, in a particular tablespace:

SELECT DISTINCT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.TABLESPACE_NAME, 
    (SELECT ROUND(SUM(S.BYTES)/1024/1024/1024) 
     FROM DBA_SEGMENTS S
     WHERE S.SEGMENT_NAME = IDXPART.INDEX_NAME
     ) GB
FROM   DBA_IND_PARTITIONS IDXPART 
WHERE  IDXPART.TABLESPACE_NAME='DATA1'
ORDER BY 4 DESC;

INDEX_OWNER	INDEX_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX1	DATA1	567
SH	SALES_IDX2	DATA1	511
SH	SALES_IDX3	DATA1	331

To check which tablespaces the different partitions in an index reside in:

SELECT TABLESPACE_NAME,COUNT(*)
FROM DBA_IND_PARTITIONS
WHERE INDEX_NAME='SALES_IDX4'
GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME	COUNT(*)
DATA1	13
DATA2	832

How to fix RMAN-04006 when cloning from active database

Background:
During an attempt to clone database using active duplication, I received an error when testing my connectivity to the target and auxiliary server. I normally do a testrun first, to see if I can connect without problems, before I start my cloning script.

My script to test the connection is simple and called "testconnection.cmd".
The connections are made through a wallet, so that you do not have to expose your passwords in any scripts or any shell prompts. See my previous post for details about setting up a wallet.

connect target /@proddb01.oric.no
connect auxiliary /@testdb01.oric.no
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
}
exit

Execute it as follows:

rman cmdfile=testconnection.cmd

connected to target database: prodb01 (DBID=2078894010, not open)
connected to auxiliary database (not started)

RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Cause:
Incorrect password in the auxiliary database

Solution:
Copy the password file from your target database server to your auxiliary database server and retry the operation.

When all is good, the output from the auxiliary database should be

connected to auxiliary database: testdb01 (not mounted)

Identity columns in Oracle 12c

The identity column introduced in Oracle 12c can be used in the different manners, according to your needs. Here are some basic examples:

create table songs (
    song_id number generated always as identity,
    title varchar2(128)
);

The "generated always" clause is default and may be omitted.

In this scenario, no inserts may contain a value for the column song_id, even if the datatype is correct. Neither null values are accepted. Oracle will generate its own value and will not allow any manipulation of the value recorded in the identity column.


Allow for the possibility to override the auto generated numbers by specifying "by default" instead of "always":

create table songs (
    song_id number generated by default as identity,
    title varchar2(128)
);

In other words, the statements below would both be permissible:

insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;

However, null cannot be specified as a legitimate value for the identity column:

insert into songs values(null, 'White wedding');

would return the error

ORA-01400: cannot insert NULL into ("MUSIC"."SONGS"."SONG_ID")

If you need to allow for NULL values in the identity column, you create the table as follows:

create table songs (
    song_id number generated by default on null as identity,
    title varchar2(128)
);

With the above definition, Oracle

* permits overriding the autogenerated numbers
* allows update of the identity column
* allows null values to be specified during inserts

All of the three statements below are thus valid:

insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;
insert into songs values(null, 'White wedding');

Tim Hall has published a more in-depth article about the same topic, as usual of the highest quality

How to run RMAN with debug information

Tested with Oracle 12.2

For example, to trace a duplicate database session, I have put the following into a file called "run_duplication.cmd":

spool trace to run_duplication.trc
spool log to run_duplication.log
set echo on;
debug on;


connect target sys/password@sourcedb
connect auxiliary sys/password@auxdb

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
DUPLICATE DATABASE "sourcedb.mydomain.com" TO auxdb
FROM ACTIVE DATABASE
TABLESPACE TOOLS
USING COMPRESSED BACKUPSET;
}
exit

When you execute your rman script:

rman cmdfile='run_duplication.cmd'

your log and trace files will be generated in your working directory.

How to use the TIMESTAMP_TO_SCN function

select timestamp_to_scn(to_timestamp('2020-05-11 14:36:22', 'YYYY-MM-DD HH24:MI:SS')) "scn" from dual;

scn
----------
20325895

Reversed:

select scn_to_timestamp(20325895) "timestamp" from dual;

timestamp
----------------------------------
11-MAY-20 02.36.20.000000000 PM

How to avoid the error Can't locate Data/Dumper.pm in @INC during installation of AHF

During installation of ahf, I received the following error:

Extracting AHF to /u01/ahf/oracle.ahf
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_per l /usr/lib64/perl5 /usr/share/perl5 . /u01/ahf/oracle.ahf/tfa/bin /u01/ahf/oracle.ahf/tfa/bin/common /u01/ahf/oracle.ahf/tfa/bin/modules /u01/ahf/oracle.a hf/tfa/bin/common/exceptions) at /u01/ahf/oracle.ahf/tfa/bin/common/tfactlshare.pm line 1350, line 1.
BEGIN failed--compilation aborted at /u01/ahf/oracle.ahf/tfa/bin/common/tfactlshare.pm line 1350, line 1.
Compilation failed in require at /u01/ahf/oracle.ahf/tfa/bin/tfasetup.pl line 107, line 1.
BEGIN failed--compilation aborted at /u01/ahf/oracle.ahf/tfa/bin/tfasetup.pl line 107, line 1.

Solution: Install missing packages:

su -
yum install perl-Data-Dumper

At this point, you need to uninstall AHF:

tfactl uninstall

Then cleanup old files. Go to the directory where you attempted to install AHF;

cd /u01/tfa
rm -rf oracle.ahf

Go to the directory where the zipped file has been extracted, and run the installation again:

/u01/oracle/patch/AHF
./ahf_setup

Potential solution to ORA-1033 during configuration of standby database in data guard broker

In my environment, the following message was displayed in dgmgrl when trying to configure a data guard environment:

DGMGRL> add database 'hdal_stb' as  connect identifier is 'hdal_stb';
Error: ORA-1033: ORACLE initialization or shutdown in progress

Failed.

I had prior to this attempt duplicated the target database to the auxiliary using "duplicate target databas for standby from active database" - and the output looked fine.

Turns out that you cannot set the parameter redo_transport_user to any other value than SYS when cloning for standby.
In my environment, we use a dedicated user for this purpuse, as we avoid using the SYS user as much as possible.

I adjusted the parameter to read SYS on both the primary and standby instance, and reran the duplication.

Afterwards, the database could be added:

DGMGRL> add database 'hdal_stb' as  connect identifier is 'hdal_stb';
Database "hdal_stb" added