Minimalistic Oracle: August 2019

Thursday, August 22, 2019

How to change location for the redo log files in physical standby database

Short background:
After a cold restore of the database files from standbyserver1 to standbyserver2, the redo logfiles where incorrectly registered in the database's control file.
This resulted in an error about the missing file every time the database opened or mounted:

Errors in file /u01/oracle/diag/rdbms/prodbb01_stby2/proddb01/trace/proddb01_m000_38117.trc:
ORA-00312: online log 3 in thread 1: /fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Since this is a standby database it is not stopping the database from functioning as intended, but it was annoying nevertheless.
Here is how I fixed the problem:

Shutdown the database, cancel managed recovery and open it in mounted mode:

sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
startup mount

Allow file renaming on the stadby by temporarily setting STANDBY_FILE_MANAGEMENT to MANUAL.

alter system set STANDBY_FILE_MANAGEMENT=manual scope=memory;

If this is not done, Oracle will throw error

ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

Rename the file(s):

alter database rename file '/fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log' to '/fra/PRODDB01_STBY2/onlinelog/o1_mf_3__df26ddnv_.log';

Depending on your configuration, start managed reply in mounted state, or open the database in real-time query mode. I am doing the latter:

alter database open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Wednesday, August 21, 2019

How to create a compressed index using advanced index compression

Applicable for Oracle 12.1 and onwards.

Basic syntax:

CREATE INDEX MYIDX1 
ON MYTABLE(COL1, COL2)
COMPRESS ADVANCED LOW;

The above statement will create a compressed index in the connected user's default tablespace, using Oracle Advanced Index Compression.

In my environment, I tried this on a B-tree index on a 62 million row table. The size dropped from 3136 to 2368 MB, a 25 percent reduction.

An index can also be altered to use advanced index compression, like this:

ALTER INDEX MYIDX1 REBUILD COMPRESS ADVANCED LOW;

If it's important to allow DML on the index during rebuild, add ONLINE, like this:

ALTER INDEX MYIDX1 REBUILD COMPRESS ADVANCED LOW ONLINE;

Oracle 12.2 documentation about advanced index compression can be found here

Documentation for the "CREATE INDEX" statement can be found here

Documentation for the "ALTER INDEX" statement can be found here

Tuesday, August 20, 2019

How to add a semicolon (or any other character) at the end of a line using vi

Open your text file in vi, and type

:%s/$/;/g

this will add a semicolon at the end of each line in your file.

Friday, August 9, 2019

How to find the number of executions for a specific SQL ID during a day

This query will gather data from DBA_HIST_ACTIVE_SESS_HISTORY for a specific day, and count the number of executions within that day:

select to_char(sample_time,'dd.mm.yyyy hh24') "day", count(*) "num executions"
from DBA_HIST_ACTIVE_SESS_HISTORY 
where sql_id = '7x0v1s9aq4y9t'
and to_date(to_char(sample_time,'dd.mm.yyyy')) = '05.08.2019'
group by to_char(sample_time,'dd.mm.yyyy hh24')
order by 1 desc;

day	num executions
05.08.2019 23	302
05.08.2019 22	285
05.08.2019 21	333
05.08.2019 20	300
05.08.2019 19	393
05.08.2019 18	255
05.08.2019 17	351
05.08.2019 16	426
05.08.2019 15	450
05.08.2019 14	624
05.08.2019 13	842
05.08.2019 12	621
05.08.2019 11	503
05.08.2019 10	461
05.08.2019 09	444
05.08.2019 08	279
05.08.2019 07	148
05.08.2019 06	79
05.08.2019 05	77
05.08.2019 04	80
05.08.2019 03	3396
05.08.2019 02	3680
05.08.2019 01	2808
05.08.2019 00	123

Thursday, August 8, 2019

How to find audit information about SELECT statements

This article is based on setup in a database running classical auditing in version 18.6.0.0.0, but should be possible to use in older versions, too.

After you have verified that your table is indeed being audited, you can move on to see exactly what was executed at a specific point in time.

To populate the columns SQL_BIND and SQL_TEXT, you need to make sure you gather extended auditing information in your database.

alter system set audit_trail=db, extended scope=spfile;
shutdown immediate 
startup

Now you can use the following query to find

SELECT TIMESTAMP, OS_USERNAME, OBJ_NAME,USERHOST, SESSIONID, USERNAME,ACTION_NAME, RETURNCODE,SQL_BIND,SQL_TEXT
FROM   DBA_AUDIT_TRAIL
WHERE  OWNER = 'SCOTT'
ORDER  BY TIMESTAMP DESC;

TIMESTAMP	OS_USERNAME	OBJ_NAME	USERHOST	SESSIONID	USERNAME	ACTION_NAME	SQL_TEXT
07.08.2019	JOHN	EMP	jonsPC	174335	DBAADMIN	SELECT	SELECT * FROM SCOTT.EMP
06.08.2019	LISA	EMP	lisaspc	171886	LISA	SELECT
05.08.2019	FRED	DEPT	fredsPC	141131	SCOTT	SELECT

How to put a mounted standby database in Real-Time Query mode

The database is currently mounted as a normal physical standby database:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

Cancel managed recovery:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

If you use the broker, the equivalent command would be

EDIT DATABASE 'proddb01_stby1' SET STATE='APPLY-OFF';

Open the database. It will automatically open in read-only mode:

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

Start managed recovery again:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

If you use the broker, the equivalent command would be

EDIT DATABASE 'proddb01_stby1' SET STATE='APPLY-ON';

Check the database status:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Before this change, the broker status showed:

DGMGRL> show database proddb01_stby1

Database - proddb01_stby1

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 9 minutes 31 seconds (computed 1 second ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF

After the change:

DGMGRL> show database proddb01_stby1

Database - proddb01_stby1

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 second (computed 0 seconds ago)
Average Apply Rate: 2.55 MByte/s
Real Time Query: ON

Monday, August 5, 2019

How to work around RMAN-08120: warning: archived log not deleted, not yet applied by standby

Problem scenario:

* You have a primary database with two standby databases.
* One of them is functional, the other one is about to be rebuilt.
* Archived redo logs are piling up on the primary.

You have already set the log_archive_dest_n to DEFERRED on the primary site, and this will prevent logs from being shipped to the standby database that is about to be rebuilt.

The reason for this is explained in Doc ID 1380368.1 on My Oracle Support: Oracle will, by default, also consider deferred locations, before deleting archive logs from the primary.

Solution:
This can be changed by setting a hidden parameter, _deferred_log_dest_is_valid, from TRUE to FALSE.

To verify that this worked as intended, I listed a couple of the archivelogs on the primary:

RMAN> list archivelog sequence between 110880 and 110881;

List of Archived Log Copies for database with db_unique_name PRODDB01
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
173550  1    110880  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc

173551  1    110881  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc

Now try to delete them from the primary server:

RMAN> delete archivelog sequence between 110880 and 110881;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=78 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=723 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=14 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=295 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc thread=1 sequence=110880
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc thread=1 sequence=110881

Oracle doesn't allow you to delete them, since they haven't been applied to all standby destinations.

To work around this, set the hidden parameter:

alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;

Try deleting them once more shows that Oracle now allows you to do so:

RMAN>  delete archivelog sequence between 110880 and 110881;

List of Archived Log Copies for database with db_unique_name proddb01
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
173550  1    110880  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc

173551  1    110881  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc RECID=173550 STAMP=1014076575
Deleted 1 objects

deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc RECID=173551 STAMP=1014076710
Deleted 1 objects

Use

delete noprompt archivelog sequence between 110880 and 110881;

to avoid having to confirm the delete operation.

How to list all objects in a database in Postgres

Use the psql meta-command \d+

psql -h myserver.mydomain.com -U music musicdb


music=> \d+
                                           List of relations
     Schema     |              Name              |   Type   |     Owner      |    Size    | Description
----------------+--------------------------------+----------+----------------+------------+-------------
 public         | pg_stat_statements             | view     | postgres       | 0 bytes    |
 music          | music_seq1                     | sequence | music          | 8192 bytes |
 music          | music_seq2                     | sequence | music          | 8192 bytes |
 music          | table1                         | table    | music          | 16 kB      |
 music          | music_seq3                     | sequence | music          | 8192 bytes |
 music          | table2                         | table    | music          | 3723 MB    |
 music          | table3                         | sequence | music          | 8192 bytes |
 music          | table4                         | table    | music          | 0 bytes    |
 music          | table5                         | table    | music          | 6117 MB    |
 music          | table6                         | table    | music          | 1884 MB    |
 music          | table7                         | table    | music          | 8192 bytes |
(24 rows)

If you only want to view the tables, use "\dt" instead of "\d+"

A good source for other size-related queries is this tutorial

How to find indexes on a table in postgres

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'myuser'
    and tablename='mytable'
ORDER BY
    tablename,
    indexname;

tablename	indexname	indexdef
mytable	idx1	CREATE INDEX idx1 ON myschema.mytableUSING btree (col1, col2)
mytable	idx2	CREATE INDEX idx2 ON myschema.mytable USING btree (col3)

Or use the psql command \d mytable, which lists the indexes after the description of the table columns

\d mytable

 
Indexes:
    "pk_mytable" PRIMARY KEY, btree (col1)
    "idx1" btree (col1, col2)
    "idx2" btree (col3)
    "idx3" btree (col3, col4)

Friday, August 2, 2019

How to list subpartitions and their sizes

First, find the number of partitions and subpartitions in a specific tablespace:

SELECT S.TABLE_NAME, S.TABLESPACE_NAME,COUNT(DISTINCT S.PARTITION_NAME) "num partitions", COUNT(DISTINCT S.SUBPARTITION_NAME) "num subpartitions"
FROM DBA_TAB_SUBPARTITIONS S 
WHERE S.TABLE_OWNER='SCOTT'
GROUP BY S.TABLE_NAME, S.TABLESPACE_NAME;

TABLE_NAME	TABLESPACE_NAME	num partitions	num subpartitions
MYTABLE1	DATA1	75	450
MYTABLE2	DATA2	73	219
MYTABLE3	DATA1	74	222
MYTABLE4	DATA2	74	222
MYTABLE5	DATA1	81	243

For a specific table, all partitions and their subpartitions:

SELECT P.PARTITION_NAME, P.SUBPARTITION_NAME, S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY P.PARTITION_NAME, P.SUBPARTITION_NAME, S.BYTES DESC;

PARTITION_NAME	SUBPARTITION_NAME	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	NUM_ROWS	LAST_ANALYZED	gb
SYS_P177766	SYS_SUBP177763	TABLE SUBPARTITION	DATA2	DISABLED	26021384	25.06.2020 22:55:36	12
SYS_P177766	SYS_SUBP177764	TABLE SUBPARTITION	DATA2	DISABLED	21531914	26.06.2020 23:32:34	9,1
SYS_P177766	SYS_SUBP177765	TABLE SUBPARTITION	DATA2	DISABLED	0	02.03.2020 00:56:19	0
SYS_P177774	SYS_SUBP177771	TABLE SUBPARTITION	DATA2	DISABLED	285	16.07.2020 23:21:12	0
SYS_P177774	SYS_SUBP177772	TABLE SUBPARTITION	DATA1	DISABLED	259	16.07.2020 23:21:30	0

For specific subpartitions:

SELECT P.PARTITION_NAME "Subpartition name", S.PARTITION_NAME,S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.SUBPARTITION_NAME in ('SYS_SUBP177773','SYS_SUBP177763','SYS_SUBP177764','SYS_SUBP177765');

PARTITION_NAME	Subpartition name	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	NUM_ROWS	LAST_ANALYZED	gb
SYS_P177766	SYS_SUBP177763	TABLE SUBPARTITION	DATA1	DISABLED	26021384	25.06.2020 22:55:36	12
SYS_P177766	SYS_SUBP177764	TABLE SUBPARTITION	DATA1	DISABLED	21531914	26.06.2020 23:32:34	9,1
SYS_P177766	SYS_SUBP177765	TABLE SUBPARTITION	DATA1	DISABLED	0	02.03.2020 00:56:19	0
SYS_P177774	SYS_SUBP177773	TABLE SUBPARTITION	DATA1	DISABLED	0	02.03.2020 00:56:20	0

For a specific partition:

SELECT S.PARTITION_NAME "Subpartition name", S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.PARTITION_NAME ='SYS_P14675'
ORDER BY S.BYTES DESC;

Subpartition name	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	NUM_ROWS	LAST_ANALYZED	gb
SYS_SUBP14672	TABLE SUBPARTITION	DATA1	DISABLED	127103150	29.06.2019	51,4
SYS_SUBP14673	TABLE SUBPARTITION	DATA1	DISABLED	89059917	29.06.2019	34,1
SYS_SUBP14674	TABLE SUBPARTITION	DATA1	DISABLED	0	29.06.2019	0

For a specific tablespace:

SELECT S.PARTITION_NAME "Subpartition name",S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.OWNER = 'SCOTT'
AND S.TABLESPACE_NAME='DATA1'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY S.BYTES DESC;

Subpartition name	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	NUM_ROWS	LAST_ANALYZED	gb
SYS_SUBP27395	TABLE SUBPARTITION	SCOTT	DISABLED	100835414	27.10.2018 19:06:53	44,6
SYS_SUBP29902	TABLE SUBPARTITION	SCOTT	DISABLED	88951135	27.10.2018 21:23:24	41,6
SYS_SUBP29332	TABLE SUBPARTITION	SCOTT	DISABLED	83142250	27.10.2018 23:40:00	38,3
SYS_SUBP29395	TABLE SUBPARTITION	SCOTT	DISABLED	78610455	27.10.2018 19:00:05	37,1
SYS_SUBP28115	TABLE SUBPARTITION	SCOTT	DISABLED	75810738	07.07.2018 15:54:52	35,7

Thursday, August 1, 2019

How to avoid RMAN-10015 when using sql-directives in an RMAN script

When attempting to use an sql directive in RMAN, you will sometimes be instructed to use the following syntax:

sql "alter session set events ' '1110 trace name errorstack level 3' '";

This will throw an error upon execution:

RMAN-03009: failure of sql command on default channel at 08/01/2019 11:20:02
RMAN-10015: error compiling PL/SQL program

Solution: do not use white spaces between the single quotes.
This will work:

sql "alter session set events ''1110 trace name errorstack level 3'' ";