AWS DMS Validation Pending for Some Tables

While running an AWS DMS task for migrating on-premise Oracle to AWS PostgreSQL RDS, some tables records are fully loaded into target database. From Table Statistics, it shows some tables validations are pending forever without progressing at all.

Check “Migration task logs”, it just says :

[TASK_MANAGER]I:All tables are loaded.waiting for validation to finish... 
                                     (replicationtask.c:1234)

RESOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

ORA-02800: Requests timed out from Oracle Scheduler Job

Get errors from running an Oracle scheduler job:

SQL> exec dbms_scheduler.run_job(job_name=>'BATCH_JOB', 
                                 use_current_session=>false);
BEGIN dbms_scheduler.run_job(job_name=>'BATCH_JOB', 
                                 use_current_session=>false); END;

*
ERROR at line 1:
ORA-02800: Requests timed out
ORA-06512: at "SYS.DBMS_ISCHED", line 196
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

WORKAROUND

1)check parameter to make sure not zero.

SQL> show parameter job_queue_processes

NAME                                 TYPE     VALUE
-------------------------------- ------------ ------------------
job_queue_processes                integer    100

2) Bounce the database due to the bugs.

3) Rerun the job successfully.

SQL> exec dbms_scheduler.run_job(job_name=>'BATCH_JOB', 
use_current_session=>false);

PL/SQL procedure successfully completed.

SEQUENCE in PostgreSQL

1)Create Sequence:

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] 
[ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ]


CREATE SEQUENCE serial START 101;

2) Select the next number from this sequence:

SELECT nextval('serial');

 nextval
---------
     101

SELECT nextval('serial');

 nextval
---------
     102

3)Sequence Manipulation Functions:

-- Return value most recently obtained with nextval 
select currval('serial') ;
-----
102
(1 row)

--Return value most recently obtained with nextval for any sequence
select lastval();
lastval
---------
102
(1 row)

-- Advance sequence and return new value
select nextval('serial') ;
-----
103
(1 row)

-- Set sequence's current value
select setval('serial', 1);
setval
--------
1
(1 row)

--setval
SELECT setval('serial', 42); Next nextval will return 43
SELECT setval('serial', 42, true); Same as above
SELECT setval('serial', 42, false); Next nextval will return 42

4) Sequence naming:

nextval('serial')   operates on sequence serial
nextval('SERIAL')   operates on sequence serial
nextval('"Serial"') operates on sequence Serial

5) Alter sequence:

ALTER SEQUENCE [ IF EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] 
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] 
[ RESTART [ [ WITH ] restart ] ] 
[ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ] 

ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER 
| SESSION_USER } 
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name 
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

6) Grant Privileges On Sequence:

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } 
TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be: 
[ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER

7)Drop sequence:

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE serial;

8) Views about sequences:

PG_SEQUENCES:

NameTypeReferencesDescription
schemanamenamepg_namespace.nspnameName of schema containing sequence
sequencenamenamepg_class.relnameName of sequence
sequenceownernamepg_authid.rolnameName of sequence’s owner
data_typeregtypepg_type.oidData type of the sequence
start_valuebigintStart value of the sequence
min_valuebigintMinimum value of the sequence
max_valuebigintMaximum value of the sequence
increment_bybigintIncrement value of the sequence
cyclebooleanWhether the sequence cycles
cache_sizebigintCache size of the sequence
last_valuebigintThe last sequence value written to disk. If caching is used, this value can be greater than the last value handed out from the sequence. Null if the sequence has not been read from yet. Also, if the current user does not have USAGE or SELECT privilege on the sequence, the value is null.

PG_SEQUENCE:

pg_sequence Columns

Name Type References Description
seqrelid oid pg_class.oid The OID of the pg_class entry for this sequence
seqtypid oid pg_type.oid Data type of the sequence
seqstart int8 Start value of the sequence
seqincrement int8 Increment value of the sequence
seqmax int8 Maximum value of the sequence
seqmin int8 Minimum value of the sequence
seqcache int8 Cache size of the sequence
seqcycle bool Whether the sequence cycles

ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, line 536 ORA-29283: invalid file operation

Connected to Oracle database through service name, then get  errors by running the below sample code.  There is no problem to run the sample code while connecting to database through IPC.

$sqlplus testuser/****@DBTEST

SQL>DECLARE
    l_output utl_file.file_type;
BEGIN
    l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
    utl_file.fclose( l_output ); 
END;
/

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
$sqlplus  / as sysdba
SQL> DECLARE
     l_output utl_file.file_type;
BEGIN
     l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
     utl_file.fclose( l_output );
END;

 /

PL/SQL procedure successfully completed.

CAUSES

New group “apex” is just added into oracle user for being able to read/write MY_DIR directory.

$ id oracle
uid=100(oracle)gid=200(oinstall)groups=200(oinstall),300(dba),400(apex)

RESOLUTION

Bounce the listener to make the changes effective.

$ ls -ltr|grep apex
drwxrwx---. 6 apexuser apex 4096 May 3 16:08 apex

$lsnrctl stop

$lsnrctl start

$sqlplus testuser/****@DBTEST

SQL>DECLARE
       l_output utl_file.file_type;
   BEGIN
       l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
       utl_file.fclose( l_output ); 
END;
/

PL/SQL procedure successfully completed.

Heterogeneous Database Connections – Oracle to PostgreSQL

There are two ways to use Heterogeneous Services,  one is via ODBC which does not require a licence,  or you can use the licensed Gateways.

This article describes how to set up the Oracle configurations using ODBC , and then you can create database link between Oracle database and AWS PostgreSQL RDS.

Subscribe to get access

Read more of this content when you subscribe today.