After successfully created the database link called “DB_LINK”, the try to access remote table with the following errors:
SQL> select count(*) from remote_user.table_name@db_link;
select count(*) from REMOTE_USER.TABLE_NAME@DB_LINK *
ERROR at line 1:
ORA-02085: database link DB_LINK connects to SOURCE_DB
$ oerr ora 02085
02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
// The connection is rejected.
// *Action: create a database link with the same name as the database it
// connects to, or set global_names=false.
//
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ---------------
global_names boolean TRUE
SOLUTION
1)Reset parameter global_names to FALSE, and recreate the database link again with any name you want.
SQL> alter system set global_names=false;
SQL> Create public database link DB_link connect to ....
SQL> select count(*) from remote_user.table_name@db_link;
COUNT(*)
----------
1000
OR
2) Create database link with the same name as source database / PDB GLOBAL_NAME.
SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------------------------
SOURCE_DB
SQL> Create public database link SOURCE_DB connect to ....
SQL> select count(*) from remote_user.table_name@source_db;
COUNT(*)
----------
1000
For connecting to PDB, you need be in the PDB to get right GLOBAL_NAME:
SQL> show con_name;
CON_NAME
------------------------------
SOURCEDBPDB
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------
SOURCEDBPDB
SQL>Create public database link SOURCEDBPDB connect to ....
SQL> select count(*) from remote_user.table_name@sourcedbpdb;
COUNT(*)
----------
1000
Oracle instance crashes with the following errors, sometimes the instance fails to start up with the same messages:
...
..
.
Errors in file /u02/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_lreg_104781.trc (incident=287152) (PDBNAME=CDB$ROOT):
ORA-07445: exception encountered: core dump [nscall()+911] [SIGSEGV] [ADDR:0x186] [PC:0x5C6F35F] [Address not mapped to object] []
Incident details in: /u02/app/oracle/diag/rdbms/ractest/RACTEST1/incident/incdir_287152/RACTEST1_lreg_104781_i287152.trc
...
..
.
Tue Aug 24 14:58:01 2021
Instance Critical Process (pid: 29, ospid: 104781, LREG) died unexpectedly
PMON (ospid: 123456): terminating the instance due to error 500
Tue Aug 24 14:58:01 2021
System state dump requested by (instance=1, osid=123456(PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u02/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_diag_104564_20210824145801.trc
Tue Aug 24 14:58:01 2021
Dumping diagnostic data in directory=[cdmp_20210824145801], requested by (instance=1, osid=123456(PMON)), summary=[abnormal instance termination].
Tue Aug 24 14:58:01 2021
Instance terminated by PMON, pid = 123456
The Call Stack Trace in the associated incident trace file ( RACTEST1_lreg_104781_i287152.trc ) shows :
In 19c GI environment, local listeners failed to start up with following errors:
[grid@racnode1 $ 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-5016: Process "/u01/app/19.0.0.0/grid/bin/lsnrctl" spawned by agent "ORAAGENT" for action "start" failed: details at "(:CLSN00010:)" in "/u01/app/grid/diag/crs/racnode1/crs/trace/crsd_oraagent_grid.trc"
CRS-5016: Process "/u01/app/19.0.0.0/grid/bin/lsnrctl" spawned by agent "ORAAGENT" for action "start" failed: details at "(:CLSN00010:)" in "/u01/app/grid/diag/crs/racnode1/crs/trace/crsd_oraagent_grid.trc"
CRS-2674: Start of 'ora.LISTENER.lsnr' on 'racnode1' failed
Further check “crsd_oraagent_grid.trc” with following errors:
Starting /u01/app/19.0.0.0/grid/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/19.0.0.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/racnode1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
TNS-01192: Missing SID_LIST_ value left of equation for SID description in LISTENER.ORA
Listener failed to start. See the error message(s) above...
Subscribe to get access
Read more of this content when you subscribe today.
Sorry! This product is not available for purchase at this time.
The ORA-1555 or Snapshot Too Old errors are reported when the read consistent images are unavailable in the Undo tablespace. This happens when there is not enough space in the Undo tablespace to retain the undo records for the long running queries.
Snapshot Too Old Error detected: SQL ID 3jgzkm92q02i8, Snapshot SCN 0x074c.3280364e, Recent SCN 0x074c.33f38d0c, Undo Tablespace UNDOTBS1, Current Undo Retention 14542.
INVESTIGATION and SOLUTION
Tune the SQL
Find sql from v$sql or dba_hist_sqltext, and tune it accordingly
SQL> select SQL_TEXT, SQL_FULLTEXT from v$sql where sql_Id='3jgzkm92q02i8';
SQL> select SQL_TEXT from DBA_HIST_SQLTEXT where sql_Id='3jgzkm92q02i8';
Check and Increase UNDO_RETENTION
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 1000
SQL> select max(maxquerylen) from v$UNDOSTAT;
MAX(MAXQUERYLEN)
----------------
1961
SQL> select max(maxquerylen) from DBA_HIST_UNDOSTAT;
MAX(MAXQUERYLEN)
----------------
22330
Set UNDO_RETENTION to the max of the above values, and make sure the UNDO tablespace has big enough space.
SQL> alter system set UNDO_RETENTION=23000;
UNDO Tablespace Size Advisor
To Get The Output using the historical information in memory :
SQL> SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;
REQUIRED_UNDO_SIZE
-----------------------------------------------------------------------------------------------------------
The Required undo tablespace size using Statistics In Memory is 2048 MB
To Get The Output using Begin/End AWR snapshot id :
SQL> SELECT 'The Required Undo tablespace size During This AWR snaps Range is ' || dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) || ' MB' required_undo_size FROM dual;
REQUIRED_UNDO_SIZE
------------------------------------------------------------------------------------------------------------
The Required Undo tablespace size During This AWR snaps Range is 2048 MB
Monitor UNDO Tablespace
The V$UNDOSTAT view holds undo statistics for 10-minute intervals, which represents statistics across instances, thus each begins time, end time, and statistics value will be a unique interval per instance.
Column name
Meaning
BEGIN_TIME
The beginning time for this interval check
END_TIME
The ending time for this interval check
UNDOTSN
The undo tablespace number
UNDOBLKS
The total number undo blocks consumed during the time interval
TXNCOUNT
The total number of transactions during the interval
MAXQUERYLEN
The maximum duration of a query within the interval
MAXCONCURRENCY
The highest number of transactions during the interval
UNXPSTEALCNT
The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests
UNXPBLKRELCNT
The number of unexpired blocks removed from undo segments to be used by other transactions
UNXPBLKREUCNT
The number of unexpired undo blocks reused by transactions
EXPSTEALCNT
The number of attempts when expired extents were stolen from other undo segments to satisfy a space request
EXPBLKRELCNT
The number of expired extents stolen from other undo segments to satisfy a space request
EXPBLKREUCNT
The number of expired undo blocks reused within the same undo segments
SSOLDERRCNT
The number of ORA-1555 errors that occurred during the interval
Make DBA Life Easy 