Minimalistic Oracle

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.