ERROR: function dblink(unknown, unknown) does not exist

From one of PostgreSQL AWS RDS instance, try to access tables of remote RDS instance with below command:

testuser=>select * from dblink('dbname=testrds port=5555
        host=testrds.rds.amazonaws.com 
        user=testuser password=passwd', 'SELECT cust_id, cust_name 
     from iamesh.customer' ) as cust ( id float, name varchar(50));

ERROR: function dblink(unknown, unknown) does not exist

LINE 1: select * from dblink('dbname=testrds port=5555 host=testrds.

HINT: No function matches the given name and argument types. 
     You might need to add explicit type casts.
SQL state: 42883
Character: 15

Show current installed extensions:

testuser=> select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10       | 11           | f              | 1.0        |           |
(1 rows)

Show available extensions, dblink extension is available to be installed:

testuser>SELECT * FROM pg_available_extensions order by 1;

        name             | default_version | installed_version |                                                       comment
-------------------------+-----------------+-------------------+----
...
..
dblink                   |1.2              |
plpgsql                  |1.0              |1.0
...
..
.

We can see dblink extension is NOT installed yet. To create “dblink” extension:

testuser>create extension dblink; 

ERROR: permission denied to create extension "dblink"
HINT: Must be superuser to create this extension.

Logon as super user, and retry again.

rdsdba=> create extension dblink;
CREATE EXTENSION

Try to access remote tables of remote instance again, and it is successful.

testuser=>select * from dblink('dbname=testrds port=5555
host=testrds.rds.amazonaws.com 
user=testuser password=passwd', 'SELECT cust_id, cust_name 
from iamesh.customer' ) as cust ( id float, name varchar(50));

     id     |                name
------------+-------------------------------------
 2001873700 | James Bond
 2001873800 | David Lee
...
..
.

Linux: find files between two date and copy into another directory

Find all the files between “2019-02-15 00:00:00” and “2019-02-18 00:00:00”.

1)Copy them into directory “/tmp/tracebackup/”.

$ find . -type f -name “*.trc” -newermt “2019-02-15 00:00:00” ! -newermt “2019-02-18 00:00:00″| xargs cp -t /tmp/tracebackup/

-t, –target-directory=DIRECTORY ; copy all SOURCE arguments into DIRECTORY

2)Tar and zip the files.

$find . -type f -name “*.trc” -newermt “2019-02-15 00:00:00” ! -newermt “2019-02-18 00:00:00″| xargs tar -czvf crs_traces.tar.gz

ORA-03262: the file is non-empty when dropping a datafile

When dropping a datafile online from a tablespace, get below error:

SQL> alter tablespace TEST_TBS drop datafile '+DG1/ractest/datafile/test_tbs.521.1000824921';
alter tablespace TEST_TBS drop datafile '+DG1/ractest/datafile/test_tbs.521.1000824921'
*
ERROR at line 1:
ORA-03262: the file is non-empty

The datafile can be dropped online only if the datafile is empty:

SQL> alter tablespace TEST_TBS add datafile '+DG1' size 1m ;

Tablespace altered.

 SQL> alter tablespace TEST_TBS drop datafile '+DG1/ractest/datafile/test_tbs.483.1000830099';

Tablespace altered.

srvctl failed to start up local listener

SYMPTOMS

After server rebooted, all Oracle Grid Infrastructure ( GI ) resources are up running as expected except local listener. Try to manually start up local listener, then get the following error messages.

$ srvctl start listener -node racnode1
PRCR-1013 : Failed to start resource ora.LISTENER.lsnr
PRCR-1064 : Failed to start resource ora.LISTENER.lsnr on node racnode1
...
..
.
CRS-2674: Start of 'ora.LISTENER.lsnr' on 'racnode1' failed

Subscribe to get access

Read more of this content when you subscribe today.