Online DBA

<<Back to Oracle Backup & Recovery Main Page recovery catalog schema release 19.04.00.00. is newer than RMAN release CAUSE The catalog schema version is different from the rman executable version. Version of RMAN client (RMAN executable) displayed when you start RMAN Run the below command as rman catalog schema owner to find the catalog schema version SQL> SELECT * FROM rcver; SOLUTION This information message can be ignored. This is simply an informational message, no action is necessary.  There will be no problem while registering the target database to the recovery catalog, resyncing the recovery catalog, and/or performing backup or recovery.   

<<Back to Oracle Backup & Recovery Main Page Datafiles are mutually inconsistent Oracle Physical Standby rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 23 11:23:32 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCLTP (DBID=XXXXX) RMAN> list failure; using target database control file instead of recovery catalog Database Role: PHYSICAL STANDBY List of Database Failures ========================= Failure ID Priority Status    Time Detected Summary ---------- -------- --------- ------------- ------- 142322     HIGH     OPEN      21-SEP-19     Datafiles are mutually inconsistent RMAN>  advise failure; Database Role: PHYSICAL STANDBY List of Database Failures ========================= Failure ID Priority Status    Time Detected Summary ---------- -------- --------- ------------- ------- 142322     HIGH     OPEN      21-SEP-19     Datafiles are mutually inconsiste

<<Back to Oracle RAC Main Page Step by Step Instruction to Patch Oracle RAC Cluster Version 12.1.0.2 Step0: download the PSU and read the readme.txt for the patch. The installation instruction and rollback instruction is provided in readme file. Transfer the PSU on each node of the RAC cluster.  Here is the extract and simplified version of Oracle  RAC patching procedure for release version 12.1.0.2 using opatchauto Step1: Ensure the Latest OPatch utility is Available Check Current OPatch Utility Version Verify if the required  OPatch utility as mentioned in readme.txt is available. $export ORACLE_HOME=/u01/product/12.1.0.2/dbhome1 $export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch $opatch version OPatch Version: 12.2.0.1.3 OPatch succeeded. Upgrade OPatch Utility to The Latest Version Download the latest OPatch utility version as mentioned in readme.txt. Ensure to upgrade the OPatch utility in all OH homes and GI home on all nodes. Latest OPatch

<<Back to Linux Main Page   How to Delete file older than 10  days $fin d /u01/log/ -type f -mtime +10 -exec rm -f {} \; Deleting files older than 10 Hours $ find /u01/log/ -type f  -mmin +600 -exec rm -f {} \; Listing top 10 files by size $du -a /u01/log / | sort -n -r | head -n 10 Counting files older than 1 days $find /u01/log/ -type f -mtime +1 |wc -l

<<Back to DB Administration Main Page ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file. Errors in file /u01/diag/rdbms/orcl1d/ORCL1D/trace/ORCL1D_j000_24858.trc: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file PSG_XVD.csv in DUMP_DATA not found Tue Sep 10 22:05:06 2019 Cause The primary cause of this issue is that an OS file for an "external table" existed at some point in time but does not now. However, the database still believes the OS file for the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there. Solution Clean up the orphaned dictionary entries. For Tempor

<<Back to PT Main Page How to Lock Statistics of a Table If due to some reasons you decided to lock the status of the table you can do it as follows. Note : Once you lock the statistics oracle no more calculates stats for those objects. To lock the table status use DBMS_STATS.LOCK_TABLE_STATS procedure. In the following example I have locked the status of EMP table in HR schema BEGIN DBMS_STATS.LOCK_TABLE_STATS ('HR','EMP'); END; / How Find the Table for Which Stats are Locked Run below query to check if the stats for the table is locked SQL> SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE STATTYPE_LOCKED IS NOT NULL AND TABLE_NAME='EMP'; OWNER                          TABLE_NAME                     STATT ------------------------------ ------------------------------ ----- HR                     EMP                           ALL Now if you run stats collection for this table you will witness following error

<<Back to Oracle DataGuard Main Page Disabling Active Data Guard Feature in Oracle Physical Standby Database. As we know oracle active data guard (also know as Physical Standby in read only with apply) is a licences feature, and one has to pay to use it. There are also several known bugs , which hits the database when using this Oracle ADG features  eg. Bug 28199085 or RMAN issues like  RMAN-03014: implicit resync of recovery catalog failed RMAN-03009: failure of partial resync command on default channel at 09/03/2019 05:56:24 RMAN-20051: datafile resync not completed So if we don't need this feature I strongly advise to keep it off (deactivated) How to deactivate the Active Data Guard Feature Deactivating of oracle active data guard feature requires setting of hidden parameter  _query_on_physical to false . Ensure to set the parameter in both primary as well as standby database. Its a static parameter and therefore will be effective only after restart

<<Back to Oracle DataGuard Main Page ORA-600 [Cursor not typechecked] Occurs In Active Standby Sites Extract of Alterlog Errors in file //u01/diag/rdbms/ORCLDs/ORCLD/trace/ORCLD_ora_20477.trc  (incident=120790) (PDBNAME=CDB$ROOT): ORA-00600: internal error code, arguments: [Cursor not typechecked], [], [], [], [], [], [], [], [], [], [], [] Incident details in: //u01/diag/rdbms/ORCLDs/ORCLD/incident/incdir_120790/ORCLD_ora_20477_i120790.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Cause:  Its a bug for more detail check out Oracle Support Doc ID 28199085.8 Bug 28199085  ORA-600 [Cursor not typechecked] Occurs In ADG Standby Sites Solution: In my test environment I resolved the issue by deactivating the Active data guard feature because we don't needed it. You can follow the steps in the post below How to Disable Active Data Guard Feature in Oracle Standby In general