Thursday, August 8, 2019

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 COMPRESS_FOR 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 COMPRESS_FOR 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 COMPRESS_FOR 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 COMPRESS_FOR 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'' ";

Wednesday, July 31, 2019

How to check if your physical standby database is applying logs or not



For Oracle versions 12.1 and lowe, use V$MANAGED_STANDBY:


SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS
FROM V$MANAGED_STANDBY
WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0'
;

CLIENT_PROCESS PROCESS THREAD# SEQUENCE# STATUS
LGWR RFS 1 116713 RECEIVING
N/A MRP0 1 114039 APPLYING_LOG

Important columns are

* PROCESS, which is the type of process whose information is being reported
MRP0 means the detached recovery server process. In this case, it is applying logs.

* CLIENT_PROCESS, which identifies the corresponding primary database process.
LGWR indicates the background log writer process

If there is a gap to be covered, you'll see the processes as being idle:

CLIENT_PROCESS PROCESS THREAD# SEQUENCE# STATUS
LGWR RFS 1 116727 IDLE
N/A MRP0 1 114115 WAIT_FOR_LOG

This is a good time to check where the gap is:
SELECT * FROM v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# CON_ID
1 114120 114121 1

This matches the alert log of the standby database:
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 114120-114121

For Oracle 12.2 and beyond, Oracle encourage DBAs to use V$DATAGUARD_PROCESS (although V$MANAGED_STANDBY is still there for backward compability).
This view "displays one row for each Oracle Data Guard process that is currently running."

According to Oracle's documentation, there should be information about a process mapped toa role called "recovery logmerger" which in the ACTION column should state "APPLYING_LOG". I have repeatedly tried to find this information without luck.

What I do find in V$DATAGUARD_PROCESS is a role called "managed recovery".
If I run these queries against my database, they seem to yield two different results, the first telling me that manged recovery is idle, the second telling me that it is applying logs:
set lines 200
select name "process name",pid "standby server PID",role,action,client_role,client_pid "primary server PID" ,task_done
from v$dataguard_process
where role like 'managed recovery'
order by role desc, action;

SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS
FROM V$MANAGED_STANDBY
WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
exit



Output:
proce standby server PID       ROLE                    ACTION       CLIENT_ROLE      primary server PID T
----- ------------------------ ----------------------- ------------ ---------------- ------------------ -
MRP0  10714                    managed recovery        IDLE         none                              0 N


CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR     RFS                1     116940 RECEIVING
N/A      MRP0               1     116940 APPLYING_LOG

Comments about this seemingly contradictory output would be much welcome.