LIST BACKUP OF database COMPLETED BETWEEN ’01-APR-2020′ and ’02-APR-2020′;
LIST BACKUP SUMMARY COMPLETED BETWEEN ’01-APR-2020′ and ’02-APR-2020′;
When using shared dblinks to do a distributed transaction from
MTS to MTS the client might see errors of the form
ORA-02050: transaction 840.21.16 rolled back, some remote DBs may be in-doubt
ORA-02068: following severe error from …
ORA-00022: invalid session ID; access denied
Shared servers closed down there by closing the dblink
Set shared_servers = max_shared_servers in the remote instance
Application team created a shared public database link, while accessing the link , they are getting below errors.
ERROR at line 1:
ORA-00022: invalid session ID; access denied
ORA-02063: preceding line from <DBLINK-NAME>
Process ID: 59963
Session ID: 291 Serial number: 12343
Please apply the below patch and let me know in case of any issues..
Patch 18759589: ESSC: ORA-22 AFTER UPGRADE FROM 18.104.22.168 TO 22.214.171.124
Release Oracle 126.96.36.199.0
Platform Linux x86-64
Following error is generated periodically in ASAP diag file:
ORA-00020: maximum number of processes (%s) exceeded.
This problem can happen for one of the following reasons in ASAP:
1) An operation requested a resource that was unavailable. The maximum number of processes is specified by the initialization parameter PROCESSES is used up. When this maximum number of process is reached, no more requests will be processed.
2) If this problem happens after adding more servers (Example, NEP servers) or NE in ASAP system or after increasing work load and problem happens during pick hour then reason and remedy will be the same as item # 1 (above)
3) If problem happens after certain interval (For example, every 3 or 4 days interval) it may cause due to connection leak and which is normally in custom cartridge code or class -A cartridge code.
Regarding the scenario # 1 and # 2, please consult with your DBA and increase the value of Oracle DB parameter called ‘processes’.
You can follow the steps below to increase the parameter ‘processes’:
1. Stop ASAP
2. Stop DB
3. Increase the Oracle DB configuration parameter ‘processes’
4. Restart the DB
5. Restart ASAP
Solution for scenario # 3:
The cartridge code needs to be reviewed to make sure all the connections from NEP/JNEP to the Oracle DB server are closing properly from the cartridge code.
ORA-20 Occurred when current process number is still far from parameter processes setting, and ges_procs used up in v$resource_limit
select * from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
—————————— ——————- ————— ——————– ——————–
processes 19766 20472 30000 30000 <============= max utilization of processes is only 20472, which is far from the max limit of 30000
sessions 19795 20499 45072 45072 <===========
enqueue_locks 21128 21234 515026 515026
enqueue_resources 1652 2169 201336 UNLIMITED
ges_procs 19761 19761 19761 19761 <================= ges_procs is used up
ges_ress 370233 1000559 799194 UNLIMITED
The same issue is investigated in
Bug 20734725 – ORA-20 ERROR REPORTED DUE TO GES_PROCS USED UP.
Which is closed as “Not a bug” but a configuration issue – _ksmg_granule_size is set too low, in this case granule size should be 512M, however it is currently set to 32MB manually.
Set a larger _ksmg_granule_size, eg 512MB when SGA_MAX_SIZE (or memory_max_target) is larger than 128GB.
SQL> alter system set “_ksmg_granule_size”=536870912 scope=spfile;
restart database is needed.
DGMGRL> show configuration;
Configuration – dg_prmydb_config
Protection Mode: MaxPerformance
stbydb – Primary database
Error: ORA-16579: bad network state detected
prmydb – Physical standby database
Fast-Start Failover: DISABLED
ERROR (status updated 0 seconds ago)
DGMGRL> show database stbydb
Database – stbydb
Intended State: TRANSPORT-ON
DGM-17016: failed to retrieve status for database “stbydb”
ORA-16662: network timeout when contacting a member
Check alert log file of respective database.
in my case alertlog file shows ASM mount point space exhausted
ORA-19504: failed to create file “+DATA1”
ORA-17502: ksfdcre:4 Failed to create file +DATA1
ORA-15041: diskgroup “DATA1” space exhausted
ARC2: Error 19504 Creating archive log file to ‘+DATA1’
I cleared some space by backup and cleaning up archive log file.
The following example demonstrates using the
SELECT INTO statement to query a single value into a PL/SQL variable, entire columns into PL/SQL collections, or entire rows into a PL/SQL collection of records:
TYPE first_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
TYPE last_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
CURSOR c1 IS SELECT first_name, last_name FROM employees;
TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
TYPE emp_typ IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
-- Query a single value and store it in a variable.
SELECT COUNT(*) INTO howmany FROM user_tables;
dbms_output.put_line('This schema owns ' || howmany || ' tables.');
-- Query multiple columns from one row, and store them in variables.
SELECT first_name, last_name INTO some_first, some_last
FROM employees WHERE ROWNUM < 2;
dbms_output.put_line('Random employee: ' || some_first ||
' ' || some_last);
-- Query a single row and store it in a record.
SELECT * INTO some_employee FROM employees WHERE ROWNUM < 2;
-- Query multiple columns from multiple rows, and store them in a collection
-- of records.
SELECT first_name, last_name BULK COLLECT INTO all_names FROM EMPLOYEES;
-- Query multiple columns from multiple rows, and store them in separate
-- collections. (Generally less useful than a single collection of records.)
SELECT first_name, last_name
BULK COLLECT INTO first_names, last_names
-- Query an entire (small!) table and store the rows
-- in a collection of records. Now you can manipulate the data
-- in-memory without any more I/O.
SELECT * BULK COLLECT INTO all_employees FROM employees;
Before buffer get tune. SQL plan was doing index fast full scan. here is you can see that
After running SQL tuning adviser that suggest to implement new profile that took index rang scan and buffer get improved dramatically also CPU cost.
as you can see above buffer get reduced from 22K to 615 and CPU cost reduced from 772 to 1. huge improvement.
Generate grants statements
select ‘grant select,insert,delete,update on ‘ || owner || ‘.’ || table_name || ‘ to app_role;’ from dba_tables where owner=’XYZ’ ;
select ‘create or replace public synonym ‘ || table_name || ‘ for ‘ || owner || ‘.’ || table_name || ‘ ;’ from dba_tables where owner=’XYZ’ ;