Skip navigation.

Michael Dinh

Syndicate content Thinking Out Loud
Michael T. Dinh, Oracle DBA
Updated: 14 hours 31 min ago

Linux Locking using flock

Thu, 2015-06-11 22:31

I am faced with a situation on how to create locking mechanism for RMAN backup.

Script dbf.sh backups database and can be run simutaneously if it’s not for the same database.

Hence dbf.sh sh running for PROD1 & PROD2 at the same time is VALID and running for PROD1 & PROD1 at the same time is NOT VALID.

While dbf.sh is running, arc.sh (backup archivelog) should not be running.

This was instigated by Laurent Schneider from his post on Can you restore from a full online backup?
http://laurentschneider.com/wordpress/2015/05/can-you-restore-from-a-full-online-backup.html

First test, dbf.sh is running and arc.sh should not.

The key is to have locking based on the database sid PROD1 and not the script.

$ crontab -l

#22 20 * * * /media/sf_working/sh/ogg_lag_sec.sh hawklas 0 > /tmp/ogg_lag_sec.sh.log 2>&1
#04 11 * * 2 [ $(date +\%d) -ge 07 ] && /home/oracle/t.sh > /tmp/t.log
* * * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 3600 /tmp/PROD1 /home/oracle/dbf.sh PROD1 >> /tmp/dbfPROD1.log 2>&1
* * * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 3600 /tmp/PROD2 /home/oracle/dbf.sh PROD2 >> /tmp/dbfPROD2.log 2>&1
* * * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 0 /tmp/PROD1 /home/oracle/arc.sh PROD1 >> /tmp/arcPROD1.log 2>&1
* * * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 0 /tmp/PROD2 /home/oracle/arc.sh PROD2 >> /tmp/arcPROD2.log 2>&1

Looks like dbf.sh was run and arc.sh was not.

$ ls -alrt /tmp/*PROD*

-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:22 /tmp/PROD1
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:22 /tmp/PROD2
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:22 /tmp/arcPROD1.log
-rw-r--r--. 1 oracle oinstall 77 Jun 11 20:22 /tmp/dbfPROD1.log
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:22 /tmp/arcPROD2.log
-rw-r--r--. 1 oracle oinstall 77 Jun 11 20:22 /tmp/dbfPROD2.log

$ cat /tmp/dbfPROD1.log

Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:22:01 PDT 2015
Sleeping 119

$ cat /tmp/dbfPROD2.log

Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:22:01 PDT 2015
Sleeping 119

Continuing to monitor the process and arc.sh never ran since dbf.sh was always running.

$ ls -alrt /tmp/*PROD*

-rw-r--r--. 1 oracle oinstall   0 Jun 11 20:22 /tmp/PROD1
-rw-r--r--. 1 oracle oinstall   0 Jun 11 20:22 /tmp/PROD2
-rw-r--r--. 1 oracle oinstall   0 Jun 11 20:22 /tmp/arcPROD1.log
-rw-r--r--. 1 oracle oinstall   0 Jun 11 20:22 /tmp/arcPROD2.log
-rw-r--r--. 1 oracle oinstall 231 Jun 11 20:25 /tmp/dbfPROD2.log
-rw-r--r--. 1 oracle oinstall 231 Jun 11 20:25 /tmp/dbfPROD1.log

$ cat /tmp/dbfPROD1.log

Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:22:01 PDT 2015
Sleeping 119
Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:24:00 PDT 2015
Sleeping 119
Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:25:59 PDT 2015
Sleeping 119

$ cat /tmp/dbfPROD2.log

Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:22:01 PDT 2015
Sleeping 119
Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:24:00 PDT 2015
Sleeping 119
Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:25:59 PDT 2015
Sleeping 119

Looking good so far. But what happens when arc.sh is currently running and then dbf.sh is started?

It would be a shame to have dbf.sh backup died because arc.sh is running.

$ crontab -l

#22 20 * * * /media/sf_working/sh/ogg_lag_sec.sh hawklas 0 > /tmp/ogg_lag_sec.sh.log 2>&1
#04 11 * * 2 [ $(date +\%d) -ge 07 ] && /home/oracle/t.sh > /tmp/t.log
43 20 * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 0 /tmp/PROD1 /home/oracle/arc.sh PROD1 >> /tmp/arcPROD1.log 2>&1
43 20 * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 0 /tmp/PROD2 /home/oracle/arc.sh PROD2 >> /tmp/arcPROD2.log 2>&1
44 20 * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 3600 /tmp/PROD1 /home/oracle/dbf.sh PROD1 >> /tmp/dbfPROD1.log 2>&1
44 20 * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 3600 /tmp/PROD2 /home/oracle/dbf.sh PROD2 >> /tmp/dbfPROD2.log 2>&1

From /usr/bin/flock -w 3600, this means wait up to 3600s before aborting dbf.sh

Let’s test this.

$ date

Thu Jun 11 20:43:06 PDT 2015

$ ls -alrt /tmp/*PROD*

-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:43 /tmp/PROD2
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:43 /tmp/PROD1
-rw-r--r--. 1 oracle oinstall 78 Jun 11 20:43 /tmp/arcPROD2.log
-rw-r--r--. 1 oracle oinstall 78 Jun 11 20:43 /tmp/arcPROD1.log

$ cat /tmp/arcPROD1.log

Starting /home/oracle/arc.sh PROD1 Thu Jun 11 20:43:01 PDT 2015
Sleeping 135s

$ cat /tmp/arcPROD2.log

Starting /home/oracle/arc.sh PROD2 Thu Jun 11 20:43:01 PDT 2015
Sleeping 135s

arc.sh started at 20:43 and is sleeping for 135s while dbf.sh is scheduled to run at 20:44

20:43 + 135s would take us to 20:45:15 which is well after the scheduled time for dbf.sh at 20:44

Let’s see if this works.

$ date

Thu Jun 11 20:45:33 PDT 2015

$ ls -alrt /tmp/*PROD*

-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:43 /tmp/PROD2
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:43 /tmp/PROD1
-rw-r--r--. 1 oracle oinstall 78 Jun 11 20:43 /tmp/arcPROD2.log
-rw-r--r--. 1 oracle oinstall 78 Jun 11 20:43 /tmp/arcPROD1.log
-rw-r--r--. 1 oracle oinstall 75 Jun 11 20:45 /tmp/dbfPROD1.log
-rw-r--r--. 1 oracle oinstall 75 Jun 11 20:45 /tmp/dbfPROD2.log

$ cat /tmp/dbfPROD1.log

Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:45:16 PDT 2015
Sleeping 1

$ cat /tmp/dbfPROD2.log

Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:45:16 PDT 2015
Sleeping 1

dbf.sh started at 20:45:16 – 1 second after arc.sh completed.

Simple scripts used to test with and you will need to modify sleep time accordingly for each test case.

$ cat dbf.sh

echo "Starting $0 $*" `date`
echo "Sleeping 1"
sleep 1

$ cat arc.sh

echo "Starting $0 $*" `date`
echo "Sleeping 135s"
sleep 135

And there you have it.

Good Night.

Reference: https://ma.ttias.be/prevent-cronjobs-from-overlapping-in-linux/


Not Another dbms_redefinition Post

Mon, 2015-06-08 21:43

If you follow me on twitter at all, then you will realize I often rant about how demos are too simplistic and do not represent real world issues.

Please allow me to demonstrate how to partitioning an existing table using DBMS_REDEFINITION.

In the real world, how tables are there without foreign keys?

What happens if there are transactions underlying the table during redef?

What happened to the NOT NULL constraints?

I really like how Oracle adds TMP$$ to the object name to avoid collision.

Don’t remember seeing this before; however, my previous test case was not as thorough.

Hopefully, all these questions will be addressed below.

$ sqlplus hr/hr @redef.sql

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 8 19:50:56 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(HR@hawklas):PRIMARY> — Drop objects to make test case re-runable

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop table parent cascade constraint purge'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop table parent_int cascade constraint purge'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop table child purge'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop materialized view log ON parent'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop materialized view parent_int'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> /*
ARROW:(HR@hawklas):PRIMARY> drop table parent cascade constraint purge;
ARROW:(HR@hawklas):PRIMARY> drop table parent_int cascade constraint purge;
ARROW:(HR@hawklas):PRIMARY> drop table child purge;
ARROW:(HR@hawklas):PRIMARY> drop materialized view log ON parent;
ARROW:(HR@hawklas):PRIMARY> drop materialized view parent_int;
ARROW:(HR@hawklas):PRIMARY> */
ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.ABORT_REDEF_TABLE(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT');

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> --
ARROW:(HR@hawklas):PRIMARY> alter session enable parallel dml;

Session altered.

ARROW:(HR@hawklas):PRIMARY> alter session force parallel dml parallel 4;

Session altered.

ARROW:(HR@hawklas):PRIMARY> alter session force parallel query parallel 4;

Session altered.

ARROW:(HR@hawklas):PRIMARY> alter session set db_file_multiblock_read_count=128;

Session altered.

ARROW:(HR@hawklas):PRIMARY> — Create test case

ARROW:(HR@hawklas):PRIMARY> create table parent(id int not null, name varchar2(30) not null, dt date not null);

Table created.

ARROW:(HR@hawklas):PRIMARY> alter table parent add constraint pk_parent primary key(id) using index;

Table altered.

ARROW:(HR@hawklas):PRIMARY> create table child(id int not null, name varchar2(30) not null);

Table created.

ARROW:(HR@hawklas):PRIMARY> alter table child add constraint fk_parent foreign key (id) references parent(id);

Table altered.

ARROW:(HR@hawklas):PRIMARY> insert into parent values (1,'one',sysdate-100);

1 row created.

ARROW:(HR@hawklas):PRIMARY> insert into parent values (2,'two',sysdate-200);

1 row created.

ARROW:(HR@hawklas):PRIMARY> insert into parent values (3,'three',sysdate-300);

1 row created.

ARROW:(HR@hawklas):PRIMARY> insert into child values(1,'another one');

1 row created.

ARROW:(HR@hawklas):PRIMARY> commit;

Commit complete.

— Get DDL for table to redef.
$ sysdba @extract_table_ddl.sql

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 9 04:43:52 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',true);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.session_transform,'CONSTRAINTS',false);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> -- SELECT dbms_metadata.get_ddl('TABLE','PARENT','HR') from dual;
ARROW:(SYS@hawklas):PRIMARY> set long 1000000 longchunksize 32000 linesize 32000 pages 0 newpage none
ARROW:(SYS@hawklas):PRIMARY> set heading off tab off echo off define off sqlprefix off blockterminator off timing off verify off feedb off

  CREATE TABLE "HR"."PARENT"
   (    "ID" NUMBER(*,0),
        "NAME" VARCHAR2(30),
        "DT" DATE
   ) ;

ARROW:(SYS@hawklas):PRIMARY> exit

UPDATE: Add using dbms_metadata to get DDL for table to be redef above.

What’s done in the real word, yes?

ARROW:(HR@hawklas):PRIMARY> — Create interval partition table for redef

ARROW:(HR@hawklas):PRIMARY> create table parent_int(id int, name varchar2(30), dt date)
  2  partition by range (dt)
  3  interval( numtoyminterval(1,'month'))
  4  (
  5  partition p0 values less than (to_date('2010-10-01', 'yyyy-mm-dd'))
  6  );

Table created.

ARROW:(HR@hawklas):PRIMARY> — Check table partitions

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, partition_name, tablespace_name, subpartition_count sub_ct, partition_position par_ct, interval int, composite com
  2  from USER_TAB_PARTITIONS where table_name='PARENT' order by partition_position asc;

no rows selected

ARROW:(HR@hawklas):PRIMARY> — Check table contraints

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, constraint_name, constraint_type, status, validated
  2  from USER_CONSTRAINTS where table_name in ('PARENT','CHILD') order by 1,3;

TABLE_NAME                     CONSTRAINT_NAME      C STATUS   VALIDATED
------------------------------ -------------------- - -------- -------------
CHILD                          SYS_C004145          C ENABLED  VALIDATED
CHILD                          SYS_C004146          C ENABLED  VALIDATED
CHILD                          FK_PARENT            R ENABLED  VALIDATED
PARENT                         SYS_C004141          C ENABLED  VALIDATED
PARENT                         SYS_C004142          C ENABLED  VALIDATED
PARENT                         SYS_C004143          C ENABLED  VALIDATED
PARENT                         PK_PARENT            P ENABLED  VALIDATED

7 rows selected.

ARROW:(HR@hawklas):PRIMARY> --
ARROW:(HR@hawklas):PRIMARY> desc parent;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER(38)
 NAME                                                                    NOT NULL VARCHAR2(30)
 DT                                                                      NOT NULL DATE

ARROW:(HR@hawklas):PRIMARY> desc child;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER(38)
 NAME                                                                    NOT NULL VARCHAR2(30)

ARROW:(HR@hawklas):PRIMARY> desc parent_int;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER(38)
 NAME                                                                             VARCHAR2(30)
 DT                                                                               DATE

ARROW:(HR@hawklas):PRIMARY> — Verify table can be redef and start redef

ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.CAN_REDEF_TABLE(uname=>USER,tname=>'PARENT',options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.START_REDEF_TABLE(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT',options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> — Copy dependencies from original to interim table
ARROW:(HR@hawklas):PRIMARY> — Using subsitution variable from SQL*Plus

ARROW:(HR@hawklas):PRIMARY> set autoprint on
ARROW:(HR@hawklas):PRIMARY> VARIABLE error_count number
ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.COPY_TABLE_DEPENDENTS(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT',num_errors=>:error_count,ignore_errors=>FALSE);

PL/SQL procedure successfully completed.


ERROR_COUNT
-----------
          0

ARROW:(HR@hawklas):PRIMARY> -- Check for errors
ARROW:(HR@hawklas):PRIMARY> SELECT count(*) FROM DBA_REDEFINITION_ERRORS;

  COUNT(*)
----------
         0

ARROW:(HR@hawklas):PRIMARY> SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_ERRORS;

no rows selected

ARROW:(HR@hawklas):PRIMARY> — Sync any outstanding transactions and finish redef

ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.SYNC_INTERIM_TABLE(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT');

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.FINISH_REDEF_TABLE(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT');

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> --
ARROW:(HR@hawklas):PRIMARY> EXEC dbms_stats.GATHER_TABLE_STATS(user,tabname=>'PARENT',cascade=>true,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'for all columns size 1',degree=>8);

PL/SQL procedure successfully completed.

Notice table PARENT contains NULLABLE columns while PARENT_INT contains NOT NULL, just the oposite of what was created.

ARROW:(HR@hawklas):PRIMARY> desc parent;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER(38)
 NAME                                                                             VARCHAR2(30)
 DT                                                                               DATE

ARROW:(HR@hawklas):PRIMARY> desc child;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER(38)
 NAME                                                                    NOT NULL VARCHAR2(30)

ARROW:(HR@hawklas):PRIMARY> desc parent_int;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER(38)
 NAME                                                                    NOT NULL VARCHAR2(30)
 DT                                                                      NOT NULL DATE

ARROW:(HR@hawklas):PRIMARY> — Check FK contraints

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, constraint_name, constraint_type, status, validated
  2  from USER_CONSTRAINTS where table_name in ('PARENT','CHILD') order by 1,3;

TABLE_NAME                     CONSTRAINT_NAME      C STATUS   VALIDATED
------------------------------ -------------------- - -------- -------------
CHILD                          SYS_C004145          C ENABLED  VALIDATED
CHILD                          SYS_C004146          C ENABLED  VALIDATED
CHILD                          TMP$$_FK_PARENT0     R DISABLED NOT VALIDATED
CHILD                          FK_PARENT            R ENABLED  NOT VALIDATED
PARENT                         SYS_C004142          C ENABLED  NOT VALIDATED
PARENT                         SYS_C004143          C ENABLED  NOT VALIDATED
PARENT                         SYS_C004141          C ENABLED  NOT VALIDATED
PARENT                         PK_PARENT            P ENABLED  VALIDATED

8 rows selected.

ARROW:(HR@hawklas):PRIMARY> SELECT constraint_name,constraint_type,table_name r_table,r_constraint_name r_constraint,
  2  status,bad,rely,validated,index_name,delete_rule
  3  from USER_CONSTRAINTS
  4  where r_constraint_name in (
  5  select constraint_name
  6  from USER_CONSTRAINTS
  7  where table_name like 'PARENT%'
  8  );

CONSTRAINT_NAME      C R_TABLE              R_CONSTRAINT         STATUS   BAD RELY VALIDATED     INDEX_NAME           DELETE_RU
-------------------- - -------------------- -------------------- -------- --- ---- ------------- -------------------- ---------
TMP$$_FK_PARENT0     R CHILD                TMP$$_PK_PARENT0     DISABLED          NOT VALIDATED                      NO ACTION
FK_PARENT            R CHILD                PK_PARENT            ENABLED           NOT VALIDATED                      NO ACTION

ARROW:(HR@hawklas):PRIMARY> — Enable validate non-FK constraints

ARROW:(HR@hawklas):PRIMARY> declare
  2    l_sql varchar2(1000);
  3  begin
  4    for x in (
  5      SELECT table_name, constraint_name from USER_CONSTRAINTS
  6      where table_name in ('PARENT','CHILD') and VALIDATED='NOT VALIDATED' and constraint_type'R'
  7    ) loop
  8      begin
  9        l_sql := 'alter table '||x.table_name||' enable validate constraint '||x.constraint_name;
 10        dbms_output.put_line (l_sql);
 11        execute immediate l_sql;
 12      exception when others then null;
 13      end;
 14  end loop;
 15  end;
 16  /
alter table PARENT enable validate constraint SYS_C004142
alter table PARENT enable validate constraint SYS_C004143
alter table PARENT enable validate constraint SYS_C004141

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> — Verify constraints

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, constraint_name, constraint_type, status, validated
  2  from USER_CONSTRAINTS where table_name in ('PARENT','CHILD') order by 1,3;

TABLE_NAME                     CONSTRAINT_NAME      C STATUS   VALIDATED
------------------------------ -------------------- - -------- -------------
CHILD                          SYS_C004145          C ENABLED  VALIDATED
CHILD                          SYS_C004146          C ENABLED  VALIDATED
CHILD                          TMP$$_FK_PARENT0     R DISABLED NOT VALIDATED
CHILD                          FK_PARENT            R ENABLED  NOT VALIDATED
PARENT                         SYS_C004142          C ENABLED  VALIDATED
PARENT                         SYS_C004143          C ENABLED  VALIDATED
PARENT                         SYS_C004141          C ENABLED  VALIDATED
PARENT                         PK_PARENT            P ENABLED  VALIDATED

8 rows selected.

ARROW:(HR@hawklas):PRIMARY> — Enable validate FK constraints

ARROW:(HR@hawklas):PRIMARY> declare
  2    l_sql varchar2(1000);
  3  begin
  4    for x in (
  5      SELECT table_name,constraint_name from user_constraints
  6      where r_constraint_name in (
  7      select constraint_name
  8      from USER_CONSTRAINTS
  9      where table_name = 'PARENT'
 10      )
 11      and status='ENABLED'
 12      and validated='NOT VALIDATED'
 13    ) loop
 14      begin
 15        l_sql := 'alter table '||x.table_name||' enable validate constraint '||x.constraint_name;
 16        dbms_output.put_line (l_sql);
 17        execute immediate l_sql;
 18      exception when others then null;
 19      end;
 20  end loop;
 21  end;
 22  /
alter table CHILD enable validate constraint FK_PARENT

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> — Verify constraints

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, constraint_name, constraint_type, status, validated
  2  from USER_CONSTRAINTS where table_name in ('PARENT','CHILD') order by 1,3;

TABLE_NAME                     CONSTRAINT_NAME      C STATUS   VALIDATED
------------------------------ -------------------- - -------- -------------
CHILD                          SYS_C004145          C ENABLED  VALIDATED
CHILD                          SYS_C004146          C ENABLED  VALIDATED
CHILD                          TMP$$_FK_PARENT0     R DISABLED NOT VALIDATED
CHILD                          FK_PARENT            R ENABLED  VALIDATED
PARENT                         SYS_C004142          C ENABLED  VALIDATED
PARENT                         SYS_C004143          C ENABLED  VALIDATED
PARENT                         SYS_C004141          C ENABLED  VALIDATED
PARENT                         PK_PARENT            P ENABLED  VALIDATED

8 rows selected.

ARROW:(HR@hawklas):PRIMARY> — Verify partitions

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, partition_name, tablespace_name, subpartition_count sub_ct, partition_position par_ct, interval int, composite com
  2  from USER_TAB_PARTITIONS where table_name='PARENT' order by partition_position asc;

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                    SUB_CT     PAR_CT INT COM
------------------------------ ------------------------------ ------------------------------ ---------- ---------- --- ---
PARENT                         P0                             USERS                                   0          1 NO  NO
PARENT                         SYS_P503                       USERS                                   0          2 YES NO
PARENT                         SYS_P502                       USERS                                   0          3 YES NO
PARENT                         SYS_P501                       USERS                                   0          4 YES NO

ARROW:(HR@hawklas):PRIMARY> — Verify index name

ARROW:(HR@hawklas):PRIMARY> SELECT index_name from USER_INDEXES where regexp_like (table_name,’^parent|^child’,’i’);

INDEX_NAME
——————————
TMP$$_PK_PARENT0
PK_PARENT

ARROW:(HR@hawklas):PRIMARY>

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
   uname                    IN  VARCHAR2,
   orig_table               IN  VARCHAR2,
   int_table                IN  VARCHAR2,
   copy_indexes             IN  PLS_INTEGER := 1,
   copy_triggers            IN  BOOLEAN     := TRUE,
   copy_constraints         IN  BOOLEAN     := TRUE,
   copy_privileges          IN  BOOLEAN     := TRUE,
   ignore_errors            IN  BOOLEAN     := FALSE,
   num_errors               OUT PLS_INTEGER,
   copy_statistics          IN  BOOLEAN     := FALSE, 
   copy_mvlog               IN  BOOLEAN     := FALSE); 

Why are Not Null Constraints not Copied by Dbms_redefinition.copy_table_dependents? (Doc ID 1089860.1)


Warning: standby redo logs not configured for thread – BUG

Thu, 2015-05-28 18:12

Surprise to find the following error for single instance Data Guard environment:

    Warning: standby redo logs not configured for thread 3 on boston

 

This will be fixed with an upcoming patch for the existing issue in Oracle Database 12.1.0.1.0 – good reason to stay up to date on patching?

Also, the number of Standby Redo Log Groups do not match since thread = 1 is being used.

Not *ALL* SRL have thread 1 assigned – not sure if this is a bug as well.

[oracle@host01 trace]$ dgmgrl /

DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> validate database boston

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    boston:  Off

DGMGRL> validate database london

  Database Role:     Physical standby database
  Primary Database:  boston

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    boston:  Off
    london:  Off

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups  
              (boston)                 (london)                 
    1         3                        2                        

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups  
              (london)                 (boston)                 
    1         3                        0                        
    Warning: standby redo logs not configured for thread 3 on boston

DGMGRL> validate database london2

  Database Role:     Logical standby database
  Primary Database:  boston

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)
    Warning: Physical and snapshot standby databases will
    be disabled if a role change is performed to this database

  Flashback Database Status:
    boston:   Off
    london2:  Off

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups  
              (boston)                 (london2)                
    1         3                        2                        

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups  
              (london2)                (boston)                 
    1         3                        0                        
    Warning: standby redo logs not configured for thread 3 on boston

DGMGRL> 
HOST01:(SYS@boston):PRIMARY> select group#,thread#,sequence#,bytes,used,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE#      BYTES       USED STATUS
---------- ---------- ---------- ---------- ---------- ----------
         4          0          0   52428800          0 UNASSIGNED
         5          0          0   52428800          0 UNASSIGNED
         6          0          0   52428800          0 UNASSIGNED
         7          0          0   52428800          0 UNASSIGNED

HOST01:(SYS@boston):PRIMARY> select count(*) from v$standby_log where thread#=1;

  COUNT(*)
----------
         0

HOST01:(SYS@boston):PRIMARY> 
HOST03:(SYS@london2):LOGICAL STANDBY> select group#,thread#,sequence#,bytes,used,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE#      BYTES       USED STATUS
---------- ---------- ---------- ---------- ---------- ----------
         4          1         67   52428800    4803072 ACTIVE
         5          1          0   52428800          0 UNASSIGNED
         6          0          0   52428800          0 UNASSIGNED
         7          0          0   52428800          0 UNASSIGNED

HOST03:(SYS@london2):LOGICAL STANDBY> select count(*) from v$standby_log where thread#=1;

  COUNT(*)
----------
         2

HOST03:(SYS@london2):LOGICAL STANDBY> 
HOST03:(SYS@london):PHYSICAL STANDBY> select group#,thread#,sequence#,bytes,used,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE#      BYTES       USED STATUS
---------- ---------- ---------- ---------- ---------- ----------
         4          1         67   52428800    4816896 ACTIVE
         5          1          0   52428800          0 UNASSIGNED
         6          0          0   52428800          0 UNASSIGNED
         7          0          0   52428800          0 UNASSIGNED

HOST03:(SYS@london):PHYSICAL STANDBY> select count(*) from v$standby_log where thread#=1;

  COUNT(*)
----------
         2

HOST03:(SYS@london):PHYSICAL STANDBY> 

Part1 Configuration Evodesk

Wed, 2015-05-20 16:47

Installation was not bad.

Evodesk is the 2nd company I am aware of for using recesss nuts for desktop installation.

This makes much more sense than using wood screws.

However, pondering if Evodeskk will be the first company to used recess nuts for all components and get rid of wood screws.

Don’t you think the legs make  great towel rack?

install1

install2


First Impression for Evodesk Desktop Unboxing

Mon, 2015-05-18 18:53

Disclaimer: I am not being paid by anyone to write positive or negative review.

Opinions are my own based on my limited engineering background.

First, packaging is somewhat poor and could be much better for a desk costing close to $1,000 ($886 for my configuration).

Tape coming off.

badpackaging2

I hope my desktop is okay.

badpackaging1

Taking a look inside. Is that a tiny scratch I see?

badpackaging3

After opening the desktop, this is the torn location – not enough foam.

badpackaging4

Look at how much love I give it.

Desktop should be shipped in bubble wrap to prevent damage and scratch.

Cable Pass Through is way too small for 30” x 72”.

smallpass

Most standing desks I was looking at are 1 inch thick.

By no means is this best in class as Evodesk Desktop is 3/4 inch thin.

You won’t find this information anywhere at Evodesk technical specification.

http://www.evodesk.com/media/desktop-diagrams.pdf

thin

This is the programmer controller.

Openned ziplock bag and was this a returned repackaged?

controller

My picture does not look at good as Evodesk – http://www.evodesk.com/standing-desks#posi-loc

I do like th Posi-Loc and was the final selling point.

Hope this is secure and does not spin.

posi-loc

Update:

It looks like Evodesk has updated the information for desktop. Either that or I was blind as a bat the first go round.

Renew™ Desktops
  • 100% reclaimed/recycled wood composite desktop
  • EvoGuard™ durable & stylish non-VOC seamless coating
  • Soft comfort edges eliminate nerve compression and pressure fatigue
  • Corners are slightly rounded for improved safety and style
  • Oversized 3” x 6” Cable Pass Through
  • Pre-drilled for quick and easy setup
  • Available sizes: 48″ (30” x 48″ x .75”), 60″ (30” x 60” x .75”), 72″ (30” x 72” x .75”)
  • Meets California Air Resources Board’s (CARB 2) stringent emission standard
  • Backed by a no-nonsense 2-year no-nonsense limited warranty

Find Contents of RMAN backuppiece

Wed, 2015-05-06 18:14

RMAN backuppiece listings from OS

oracle@arrow:hawklas:/home/oracle
$ ll /oradata/backup/
total 216088
-rw-r-----. 1 oracle oinstall  1212416 May  5 11:06 DBF_HAWK_3130551611_20150505_hjq65thu_1_1_KEEP
-rw-r-----. 1 oracle oinstall 50536448 May  5 11:07 DBF_HAWK_3130551611_20150505_hkq65thu_1_1_KEEP
-rw-r-----. 1 oracle oinstall 39059456 May  5 11:07 DBF_HAWK_3130551611_20150505_hlq65thv_1_1_KEEP
-rw-r-----. 1 oracle oinstall  5529600 May  5 11:07 DBF_HAWK_3130551611_20150505_hmq65tie_1_1_KEEP
-rw-r-----. 1 oracle oinstall  1785856 May  5 11:07 DBF_HAWK_3130551611_20150505_hnq65tit_1_1_KEEP
-rw-r-----. 1 oracle oinstall    98304 May  5 11:07 DBF_HAWK_3130551611_20150505_hoq65tjd_1_1_KEEP
-rw-r-----. 1 oracle oinstall     2560 May  5 11:07 DBF_HAWK_3130551611_20150505_hpq65tjf_1_1_KEEP
-rw-r-----. 1 oracle oinstall  1343488 May  5 11:07 DBF_HAWK_3130551611_20150505_hqq65tjh_1_1_KEEP
-rw-r-----. 1 oracle oinstall  1212416 May  4 19:43 HAWK_3130551611_20150504_h9q647ee_1_1
-rw-r-----. 1 oracle oinstall 39051264 May  4 19:43 HAWK_3130551611_20150504_haq647ee_1_1
-rw-r-----. 1 oracle oinstall 50315264 May  4 19:43 HAWK_3130551611_20150504_hbq647ef_1_1
-rw-r-----. 1 oracle oinstall  5529600 May  4 19:43 HAWK_3130551611_20150504_hcq647em_1_1
-rw-r-----. 1 oracle oinstall  1785856 May  4 19:43 HAWK_3130551611_20150504_hdq647ep_1_1
-rw-r-----. 1 oracle oinstall   285184 May  4 19:43 HAWK_3130551611_20150504_hfq647ev_1_1
-rw-r-----. 1 oracle oinstall  1088000 May  4 19:43 HAWK_3130551611_20150504_hgq647ev_1_1
-rw-r-----. 1 oracle oinstall   280064 May  4 19:43 HAWK_3130551611_20150504_hhq647f0_1_1
-rw-r-----. 1 oracle oinstall 11075584 May  4 19:43 HAWK_c-3130551611-20150504-0e
-rw-r-----. 1 oracle oinstall 11075584 May  4 19:43 HAWK_c-3130551611-20150504-0f

Let’s find the backupset and content of backupset for backuppiece.

oracle@arrow:hawklas:/home/oracle
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 6 17:02:57 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3130551611)

RMAN> list backuppiece '/oradata/backup/HAWK_3130551611_20150504_hbq647ef_1_1';

using target database control file instead of recovery catalog

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
555     554     1   1   AVAILABLE   DISK        /oradata/backup/HAWK_3130551611_20150504_hbq647ef_1_1

RMAN> list backupset 554;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
554     Full    47.98M     DISK        00:00:09     2015-MAY-04 19:43:20
        BP Key: 555   Status: AVAILABLE  Compressed: YES  Tag: TAG20150504T194309
        Piece Name: /oradata/backup/HAWK_3130551611_20150504_hbq647ef_1_1
  List of Datafiles in backup set 554
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2       Full 1946389    2015-MAY-04 19:43:11 /oradata/HAWKLAS/datafile/o1_mf_sysaux_bg5n9c44_.dbf

RMAN>

The backuppiece is from FULL database backup containing datafile for tablespace SYSAUX.


Backup file using copy with date append

Fri, 2015-05-01 21:51

How many ways are there to backup a file? Copy and append .bak, .sav, .orig, etc …

I was looking for a simple, consistent, yet elegant way to do this.

Good thing for Google (disclaimer I do own the stock), I came across this blog – http://blog.justin.kelly.org.au/how-to-copy-file-and-append-current-date-in-l/

Demo:

oracle@arrow:hawklas:/home/oracle
$ ps
  PID TTY          TIME CMD
 4343 pts/1    00:00:00 bash
 6915 pts/1    00:00:00 ps

oracle@arrow:hawklas:/home/oracle
$ ls -l logdump*
-rw-r--r--. 1 oracle oinstall 2487 Apr 19 11:09 logdump.hst

oracle@arrow:hawklas:/home/oracle
$ filename=logdump.hst

oracle@arrow:hawklas:/home/oracle
$ cp $filename{,.`date +%d%b%Y-%H%M`}

oracle@arrow:hawklas:/home/oracle
$ ls -l logdump*
-rw-r--r--. 1 oracle oinstall 2487 Apr 19 11:09 logdump.hst
-rw-r--r--. 1 oracle oinstall 2487 May  1 20:31 logdump.hst.01May2015-2031

In Search of Standing Desk

Wed, 2015-04-29 17:07

I came across Evodesk Standing Desk Review
http://www.workwhilewalking.com/evodesk-standing-desk-review

Could not resist the temptation but reach out to @TreadmillDesker
You bark loud but how is ThermoDesk ELITE better EVODESK other than motor? $477=1333-886 is a lot for a motor. Let’s see pic.

Here is the respond I got back.
Thanks for the question! lab test of the Evo’s base concluded: slower speed, louder motors, and instability at taller heights.

Notice the respond was very vague. Slower speed comparing to? Louder motors comparing to? Instability comparing to?

Keep in mind @TreadmillDesker recommends ThermoDesk ELITE which it has an affiliation with and wonder if there is a bias here.

In my opinion, if a website is to perform a review and critique other products it should provide sufficient data.

Videos and pictures would be great.

Here’s is another marketing gimmick from @TreadmillDesker
Did You Know Office Fitness Can Be Tax Deductible?

Looks like I pinched another nerve and responded.
@TreadmillDesker you need to be clear that tax deductible is not the same as tax deduction. please don’t use tax deductible to lure people.

An item that is tax deductible means it may be included in the expense for a possible tax deduction but does not necessitate a tax deduction.
First, the individual would need to itemized. Next, only expenses exceeding 2% of AGI qualifies.
The likelihood of one getting a tax deduction is less than 10% and it’s not a truly a full deduction.

You might ask, what qualifies me to make this assessment. I am a retired Tax Advisor after 19 yrs of experience.

Don’t get me wrong, I really like ThermoDesk ELITE and in all fairness, the review was “perhaps most entertaining”

If there were compatibilities between the 2 companies, I would buy components from both to build the desk.

Lastly, here is a price comparison for the two desks,
ThermoDesk ELITE has a 50+% price premium but is there a 50% increase in performance, product, or quality?

EVODESK
Desktop Size: 30×72
$787
$99 (shipping)
$886

ThermoDesk ELITE – Electric 3D-Adjustable Desk with 72″ Tabletop
$1,234
$99.00 (shipping)
$1,333

Disclaimer: I do not have any affiliation with either companies nor am I compensated by any means for this post.


Find Users with DBA Roles

Thu, 2015-04-02 18:24

This sounds like a simple request doesn’t it?

Simple if there are no roles which are granted DBA role and allow me to show what can go wrong.

Create ROLE “secret”, Grant DBA to “secret”, Grant “secret” to USER “michael”

ARROW:(SYS@hawklas):PRIMARY> create role secret;

Role created.

ARROW:(SYS@hawklas):PRIMARY> grant dba to secret;

Grant succeeded.

ARROW:(SYS@hawklas):PRIMARY> grant secret to michael identified by michael;

Grant succeeded.

ARROW:(SYS@hawklas):PRIMARY>

When a simple SQL is used, “secret” is a ROLE NOT USER.

ARROW:(SYS@hawklas):PRIMARY> r
  1  SELECT grantee
  2  FROM dba_role_privs
  3  WHERE granted_role='DBA'
  4  AND NOT regexp_like(grantee,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
  5*

GRANTEE
------------------------------
MDINH
HR
GGADMIN
TESTING
SECRET

ARROW:(SYS@hawklas):PRIMARY>

Easy enough, let’s join dba_role_privs and dba_users. Where’s “Michael” at?

ARROW:(SYS@hawklas):PRIMARY> r
  1  SELECT  r.grantee
  2  FROM  dba_role_privs r, dba_users u
  3  WHERE r.grantee=u.username
  4  AND r.granted_role='DBA'
  5  AND NOT regexp_like(r.grantee,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
  6  ORDER BY 1
  7*

GRANTEE
------------------------------
GGADMIN
HR
MDINH
TESTING

ARROW:(SYS@hawklas):PRIMARY>

We found “Michael”

ARROW:(SYS@hawklas):PRIMARY> r
  1  WITH u AS (
  2  SELECT username
  3  FROM dba_users
  4  WHERE NOT regexp_like(username,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
  5  )
  6  SELECT distinct username
  7  FROM   u, dba_role_privs d
  8  WHERE  username = d.grantee
  9  OR    (d.granted_role='DBA' AND d.granted_role in (SELECT role FROM role_role_privs WHERE granted_role='DBA'))
 10  ORDER BY 1
 11*

USERNAME
------------------------------
GGADMIN
HR
MDINH
MICHAEL
TESTING

ARROW:(SYS@hawklas):PRIMARY>

Updated:

Came across a blog which has another solution.

http://otipstricks.blogspot.com/2011/03/who-has-dba-privs.html

ARROW:(SYS@hawklas):PRIMARY> r
  1  select username, 1 level_deep from V$PWFILE_USERS
  2  union
  3  select grantee, max(level_deep) from (
  4  select distinct level level_deep, grantee, granted_role
  5  from dba_role_privs
  6  start with granted_role='DBA'
  7  connect by prior grantee=granted_role
  8  ) where grantee in (select username from dba_users)
  9  group by grantee
 10* order by 1

USERNAME                       LEVEL_DEEP
------------------------------ ----------
GGADMIN                                 1
HR                                      1
MDINH                                   1
MICHAEL                                 2
SYS                                     1
SYSTEM                                  1
TESTING                                 1

7 rows selected.

ARROW:(SYS@hawklas):PRIMARY>

Note: regexp_like is available from 10g onward.

Another update.

ARROW:(SYS@hawklas):PRIMARY> SELECT MAX(level_deep) level_deep, grantee users, granted_role
  2  FROM
  3    ( SELECT DISTINCT level level_deep, grantee, granted_role
  4      FROM dba_role_privs
  5      START WITH granted_role ='DBA'
  6      CONNECT BY prior grantee=granted_role
  7     )
  8  WHERE grantee IN (SELECT username FROM dba_users)
  9  GROUP BY grantee, granted_role
 10  ORDER BY 1,2
 11  ;

LEVEL_DEEP USERS                          GRANTED_ROLE
---------- ------------------------------ ------------------------------
         1 GGADMIN                        DBA
         1 HR                             DBA
         1 MDINH                          DBA
         1 ORACLE                         DBA
         1 SYS                            DBA
         1 SYSTEM                         DBA
         1 TESTING                        DBA
         2 MICHAEL                        SECRET
         3 SYS                            TOPSECRET
         3 TOPS                           TOPSECRET

10 rows selected.

ARROW:(SYS@hawklas):PRIMARY> SELECT MAX(level_deep) level_deep, grantee roles, granted_role
  2  FROM
  3    ( SELECT DISTINCT level level_deep, grantee, granted_role
  4      FROM dba_role_privs
  5      START WITH granted_role ='DBA'
  6      CONNECT BY prior grantee=granted_role
  7     )
  8  WHERE grantee NOT IN (SELECT username FROM dba_users)
  9  GROUP BY grantee, granted_role
 10  ORDER BY 1,2
 11  ;

LEVEL_DEEP ROLES                          GRANTED_ROLE
---------- ------------------------------ ------------------------------
         1 SECRET                         DBA
         2 TOPSECRET                      SECRET

ARROW:(SYS@hawklas):PRIMARY>

Be Careful when using FRA with Streams

Fri, 2015-03-27 16:12

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production

select state from gv$streams_capture;

STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------
WAITING FOR REDO: LAST SCN MINED 442455793041

select thread#, sequence#, status
from v$archived_log
where 442455793041 between first_change#
and next_change# order by 1,2;

   THREAD#  SEQUENCE# S
---------- ---------- -
	 1    1070609 D
	 1    1070609 D
	 1    1070609 D
	 1    1070610 D
	 1    1070610 D
	 2    1153149 D
	 2    1153149 D
	 2    1153149 D

8 rows selected.

Who’s deleting the archived logs? Thanks to Praveen G. who figured this out. From the alert log.

WARNING: The following archived logs needed by Streams capture process
are being deleted to free space in the flash recovery area. If you need
to process these logs again, restore them from a backup to a destination
other than the flash recovery area using the following RMAN commands:
   RUN{
      # <directory/ASM diskgroup> is a location other than the
      # flash recovery area
      SET ARCHIVELOG DESTINATION TO '<directory/ASM diskgroup>';
      RESTORE ARCHIVELOG ...;
   }