Database
Great opportunity to upgrade your career
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 4 years ago Comments: 0

Great opportunity to upgrade your career

Oracle University is offering 25% off Oracle Learning Subscriptions and career-enhancing Oracle Certification Vouchers.

 

This offer covers training on: 
  • Oracle Cloud (IaaS/PaaS/SaaS)
  • Oracle Database
  • Oracle Systems
  • MySQL
  • Java Programming
  • Oracle Middleware
  • Oracle Applications
  • Oracle Industries
To access this discount click here
 
We hope we helped !!!
 
Andre Ontalba and Rodrigo Mufalani

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

 


Login failed due to ORA-28030: ORA-28030: Server encountered problems accessing LDAP directory service
Category: Database Author: Rodrigo Mufalani (Board Member) Date: 5 years ago Comments: 0

Login failed due to ORA-28030: ORA-28030: Server encountered problems accessing LDAP directory service

 
 
Today I noticed in my environment a lot of messages ORA-28030 in the alert.log of the one database version 18c, previously configured to authenticate on OID (Oracle Internet Directory), event with removal of the configuration, one parameter still configured and that was the reason of the messages. 
Also, I checked on listener.log and no failures occurred.
 
I try to find useful information regargind this error, but just found issues on the oposite direction, people configuring authentication on OID, our issue was cause because of removal, not completely, the configuration of OID.
 
So, I decided to write this small post about the issue and how to solve.
 
Before, one of the steps to configure OID is setting the parameter LDAP_DIRECTORY_ACCESS = PASSWORD, so one of my coleagues have done that:
 
alter system set LDAP_DIRECTORY_ACCESS = 'PASSWORD' scope=both sid='*';
— Note that this parameter is dynamic
 
To solve the issue, I issue the parameter as default:
alter system set LDAP_DIRECTORY_ACCESS = 'NONE' scope=both sid='*';
Immediately, after the command, no more messages “Login failed due to ORA-28030: ORA-28030: Server encountered problems accessing LDAP directory service”. 
 
I hope this helps you to avoid this messages when you are removing OID authentication of one database.
 
You can also find more useful information on
 
Deinstall
https://docs.oracle.com/en/middleware/lifecycle/12.2.1.3/inoim/uninstalling-or-reinstalling-product.html#GUID-3FC6A4E1-4684-4BAC-9760-DA308A114FE7
Install
https://docs.oracle.com/en/middleware/lifecycle/12.2.1.3/inoim/installing-product-software.html#GUID-5D47117E-3983-4BED-BADB-CB7DDD63DB34
 
All the Best,
Rodrigo Mufalani

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited 
to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

 


Monitoring Data Guard operation
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

Monitoring Data Guard operation

As we saw in the last article on installing DataGuard in Oracle 19C, we will now see how to monitor Data Guard.
 
 
— This script is to be run on the Standby of a Data Guard Physical Standby Site

 

set echo off
set feedback off
column timecol new_value tstamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol from sys.dual;
column output new_value dbname
select value || '_' output from v$parameter where name = 'db_name';
— Output the results to this file
spool dg_Standby_diag_&&dbname&&tstamp
set lines 132
set pagesize 500
set numformat 999999999999999
set trim on
set trims on
— Get the current Date
set feedback on
select systimestamp from dual;

-- Standby Site Details
set heading off
set feedback off
select 'Standby Site Details' from dual;
select '********************' from dual;
set heading on
set feedback on

col db_unique_name format a15
col flashb_on format a10

select DB_UNIQUE_NAME,DATABASE_ROLE DB_ROLE,FORCE_LOGGING F_LOG,FLASHBACK_ON FLASHB_ON,LOG_MODE,OPEN_MODE,
GUARD_STATUS GUARD,PROTECTION_MODE PROT_MODE
from v$database;
— Current SCN – this value on the primary and standby sites where real time apply is in place should be nearly the same

 

select DB_UNIQUE_NAME,SWITCHOVER_STATUS,CURRENT_SCN from v$database;

-- Incarnation Information
--

set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on

select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;

set heading off
set feedback off
select 'Archive Destination Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
-- Current Archive Locations
--

column host_name format a30 tru
column version format a10 tru
select INSTANCE_NAME,HOST_NAME,VERSION,ARCHIVER from v$instance;

column destination format a35 wrap
column process format a7
column archiver format a8
column dest_id format 99999999

select DEST_ID,DESTINATION,STATUS,TARGET,ARCHIVER,PROCESS,REGISTER,TRANSMIT_MODE
from v$archive_dest
where DESTINATION IS NOT NULL;

column name format a22
column value format a100
select NAME,VALUE from v$parameter where NAME like 'log_archive_dest%' and upper(VALUE) like 'SERVICE%';

set heading off
set feedback off
select 'Archive Destination Errors' from dual;
select '**************************' from dual;
set heading on
set feedback on

column error format a55 tru
select DEST_ID,STATUS,ERROR from v$archive_dest
where DESTINATION IS NOT NULL;

column message format a80
select MESSAGE, TIMESTAMP
from v$dataguard_status
where SEVERITY in ('Error','Fatal')
order by TIMESTAMP;

-- Redo Log configuration
-- The size of the standby redo logs must match exactly the size on the online redo logs

set heading off
set feedback off
select 'Data Guard Redo Log Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on

select GROUP# STANDBY_GROUP#,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS from v$standby_log order by GROUP#,THREAD#;

select GROUP# ONLINE_GROUP#,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log order by GROUP#,THREAD#;
— Data Guard Parameters

set heading off
set feedback off
select 'Data Guard Related Parameters' from dual;
select '*****************************' from dual;
set heading on
set feedback on

column name format a30
column value format a100
select NAME,VALUE from v$parameter where NAME IN ('db_unique_name','cluster_database','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','fal_client','fal_server','log_archive_config','log_archive_trace','log_archive_max_processes','archive_lag_target','remote_login_password_file','redo_transport_user') order by name;
— Managed Recovery State

 

set heading off
set feedback off
select 'Data Guard Apply Status' from dual;
select '***********************' from dual;
set heading on
set feedback on

select systimestamp from dual;

column client_pid format a10
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

host sleep 10

select systimestamp from dual;

select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

host sleep 10

select systimestamp from dual;

select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

set heading off
set feedback off
select 'Data Guard Apply Lag' from dual;
select '********************' from dual;
set heading on
set feedback on

column name format a12
column lag_time format a20
column datum_time format a20
column time_computed format a20
SELECT NAME, VALUE LAG_TIME, DATUM_TIME, TIME_COMPUTED
from V$DATAGUARD_STATS where name like 'apply lag';
— If there is a lag remove the comment for the select below

 

-- SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;

set heading off
set feedback off
select 'Data Guard Gap Problems' from dual;
select '***********************' from dual;
set heading on
set feedback on

select * from v$archive_gap;

set heading off
set feedback off
select 'Data Guard Errors in the Last Hour' from dual;
select '**********************************' from dual;
set heading on
set feedback on

select TIMESTAMP,SEVERITY,ERROR_CODE,MESSAGE from v$dataguard_status where timestamp > systimestamp-1/24;
spool off

 



Hope this helps. See you !!!
 
André  Ontalba  – www.dbadutra.com
 
 
 
 
 
 

 Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

 


Query the last patch applied on database 11g, 12cR1, 12cR2 and 18c
Category: Database Author: Rodrigo Mufalani (Board Member) Date: 5 years ago Comments: 0

Query the last patch applied on database 11g, 12cR1, 12cR2 and 18c

 
 
Part of DBA routine is patch databases by quarterly, what is that means? 
 
It means that Oracle is a software and it is hard to hear, but Oracle database has bugs and secutiry vulnerabilities, so is good idea patch regularly your production databases with RU and RUR, you can
find more information about this change on nomenclature that occurred on 12cR2 at this of link on blog of the master https://mikedietrichde.com/2017/11/07/ru-rur-recommendations-facts/
So, usually a patch came with 2 different parts, first part is the binaries (libraries, programs and etc), you must be familiar with opatch utility to apply a patch on the Oracle Home, and the second part is the data dictionary corrections. 
To execute this part, since Oracle version 12cR1 we use datapatch to apply, this tool is amazing, really like to use it. So, the datapatch utility, is part of OPatch and the purpose of usage of this tool is apply scripts against Oracle data dictionary.
In this article I will show to you, how to query the correct views on the database to find informations about 11g, and 12c (family) databases, let’s say like that.
 
So, on version 11g, after apply a PSU patch on database, after run the script catbundle.sql, you can query this view with SYS account, to check information about the past PSUs applied:

— 11g version

set lin 1000
col action form a12
col version form a40
col comments form a70
col action_date form a20

select comments, action, to_char(action_time,’DD/MM/RR HH24:MI:SS’) action_date, version
from registry$history
order by action_date;

COMMENTS ACTION ACTION_DATE VERSION
———————————————————————- ———— ——————– —————————————-
Patch applied from 18.1.0.0.0 to 18.3.0.0.0 RU_APPLY 20/03/19 14:36:26 18.0.0.0.0
OJVM RU post-install APPLY 20/03/19 14:37:42 18.3.0.0.180717OJVMRU
RAN jvmpsu.sql jvmpsu.sql 20/03/19 14:37:42 18.3.0.0.180717OJVMRU
RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627 BOOTSTRAP 18

 

For the version 12R1 and onwards you will need to use another view and depending against wich version are running you can filter by PSU ou RELEASE words on column description:
 

— For 12c and 18c
set lin 1000
col action form a12
col version form a40
col description form a85
col action_date form a20

select description, action, to_char(action_time,’DD/MM/RR HH24:MI:SS’) action_date, ‘ ‘ version
from dba_registry_sqlpatch;

DESCRIPTION ACTION ACTION_DATE VERSION
————————————————————————————- ———— ——————– —————————————-
Database Release Update : 18.3.0.0.180717 (28090523) APPLY 20/03/19 14:37:46
OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415) APPLY 20/03/19 14:37:46

 

Obs.: There is no more version column, I keep that just for have compatibility with some old scripts.
 

To query:
— 12cR1
where upper(description) like ‘DATABASE PSU%’;

— 12cR2 onwards
where upper(descrition) like ‘DATABASE RELEASE%’;

 

I hope this brief note can help you to find more details how to query and check if the version applied on binaries matches the version of the database level of patch.
 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

All the best,
Rodrigo Mufalani