WARNING: too many parse errors in oracle alert log

WARNING: Too many parse errors in the Oracle alert log. Sometimes, the alert log reports some parse errors (in my case, ORA-00937). But most of the time, you can't find the problematic query easily. In my case, the sqlid does not exist anymore in the memory. Actually, I don't even know if Oracle stores this …

ORACLE – FIND USERS WITH DBA ROLE

Only selecting DBA_ROLE_PRIVS view isn't enough to find users with DBA role because DBA ROLES can be hidden by other roles. For example, bellow we have the role DBA_TEAM and DBA_THIRD_PARTY_TEAM. The DBA role is granted to DBA_TEAM and DBA_TEAM is granted to DBA_THIRD_PARTY_TEAM . After that we grant DBA_THIRD_PARTY_TEAM to marcos user. SQL> create …

Datapump – how to export a percent of the available data

It's a common dba task to refresh a dev database using data pump, usually at schema level. But the dev team really need all the updated data? in some cases no, but you should ask the dev team first. Also, there are some cases where we only need to create a database for a specific …

VIRTAL MACHINE CPU ISSUE IN VIRTUALBOX AND VMWARE on windows – smp: csd: Detected non-responsive CSD lock

Hi, this is a different post I decided to bring to the blog. Recently, I have purchased a new laptop and I've moved all virtual machines (my labs) from old laptop to this new laptop, where the processor is a intel core i5 11th. But, for some reason, no machine has worked fine as worked …

Oracle – O que é INITRANS? e MAXTRANS?

Resumo: número INIcial e MÁXimo de TRANSações permitidas em um bloco de dados concorrentemente. INITRANS O parâmetro INITRANS define a quantidade de ITL (Interested Transaction List) inicial quando um novo bloco de dados é criado. Um ITL slot é necessário para qualquer sessão que precise modificar um bloco de dados. O valor default do INITRANS …

Oracle – How to convert global index to local index

Hey there! Recently I had to do this task in our customer and I've found this method. The Oracle Version was 12.2. What you need to do is: Create the new index as local and invisible. But will this work in a already indexed column? Yes! since the index is another type (I mean, from …

Reading mysqldump output file compressed with gzip on linux

I usually need to do it when I'm creating a slave. I need to be able to read a mysql dump file generated by mysqldump command compressed with gzip command. For example: mysqldump --single-transaction --events --quick --compress --routines --master-data=2 --databases sonda xi --add-drop-database | gzip > /backup/teste_backup.sql.gz When I am creating a slave, I use …

ORA-39083 and ORA-01917 during grant role – import oracle

The error: Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - ProductionMaster table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_FULL_01": userid="/******** AS SYSDBA" directory=exp dumpfile=moot_%U.dmp logfile=importmoot.log parallel=4 full=y cluster=nProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTORA-39083: Object type ROLE_GRANT failed to create with error:ORA-01919: role 'ADMIN_MOOT' does not existFailing sql is: GRANT "ADMIN_MOOT" TO "MOOT"Processing …

RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

This can happen during a duplicate database, for example: (dg1)(oracle@jupiter):admin> rman target sys/danilo123@jupiter auxiliary sys/danilo123@jupiterstb Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 10 18:10:58 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: JUPITER (DBID=4201888259) RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== …

Oracle script – Number of redo log groups by thread# and type

Just sharing other script in my blog. This script shows the number of redo log groups (online log and standby log) by thread# and type. --Written by: astoradba.wordpress.com select m.type,g.thread#,count(DISTINCT g.group#) NUMBER_OF_GROUPS from v$log g inner join v$logfile m on m.group#=g.group# group by m.type,g.thread# union all select m.type,g.thread#,count(DISTINCT g.group#) NUMBER_OF_GROUPS from v$standby_log g inner join …

ORA-01111,ORA-01110,ORA-01157 in the physical standby database

This erro usually happen when the parameter standby_file_management is defined as manual or db_create_file_dest is not set or db_file_name_convert is not set. From SQL plus (ie: standby_file_management is manual): show parameters standby_file_management From dgmgrl utility: show database your_standby_db StandbyFileManagement; Documentation. When automatic standby file management is enabled, operating system file additions and deletions on the …

ORA-10458,ORA-01152 and ORA-01110 after recover standby database from service

RMAN> recover standby database from service service_name_here; Starting recover at 05-MAR-21 using target database control file instead of recovery catalog Oracle instance started Total System Global Area 1291842528 bytes Fixed Size 8647648 bytes Variable Size 503316480 bytes Database Buffers 771751936 bytes Redo Buffers 8126464 bytes contents of Memory Script: { restore standby controlfile from service …