News
Entertainment
Science & Technology
Life
Culture & Art
Hobbies
News
Entertainment
Science & Technology
Culture & Art
Hobbies
Both Success and Failure Logon are audited by unified audit policy ORA_LOGON_FAILURES – Make DBA Life Easy
After running the following statements to disable and enable audit policy ORA_LOGON_FAILURES. SQL> noaudit policy ORA_LOGON_FAILURES;SQL> audit policy ORA_LOGON_FAILURES; Cause The command of audit policy is lack of WHENEVER NOT SUCCESSFUL option, therefore audit policy of ORA_LOGON_FAILURES will audit both LOGON success and LOGON failure. Solution In order to set ORA_LOGON_FAILURES to audit LOGON failure…
Standby Database: *.bin files are observed in $ORACLE_BASE/audit/$ORACLE_SID/ – Make DBA Life Easy
Symptoms There are many *.bin files are observed in $ORACLE_BASE/audit/$ORACLE_SID. For example: $ ls -ltrtotal 666644-rw-------. 1 oracle oinstall 10240512 Sep 19 15:09 ora_audit_1212246579_3985105844.bin-rw-------. 1 oracle oinstall 10241536 Sep 20 06:52 ora_audit_1212303144_3688912851.bin-rw-------. 1 oracle oinstall 20480 Sep 20 12:02 ora_audit_1212321763_3431476539.bin-rw-------. 1 oracle oinstall 12288 Sep 20 12:35 ora_audit_1212323740_3065638769.bin-rw-------. 1 oracle oinstall 10241024 Sep 20 22:35…
Standby Database: arch1_.dbf in $ORACLE_HOME/dbs/ folder – Make DBA Life Easy
There are a lof of arch1_.dbf files in $ORACLE_HOME/dbs/ folder, after standby database recovery area is full. We can just delete them , or leave standby database to clean up after standby database is in sync with primary database. If manually delete them, crosscheck then accordingly: RMAN > crosscheck archivelog all;
How to Unregister a Database from RMAN Repository – Make DBA Life Easy
Target Database is Still Available You connect to the target database TEST and then unregister it: rman TARGET SYS/Passwd@TEST CATALOG rman/rman@catdbRMAN> UNREGISTER DATABASE NOPROMPT; Unregistering a Database That is Not Unique in Catalog or Target Database is Unvailable The following UNIX shell script unregisters database testdb from the recovery catalog. Because multiple databases called testdb are registered in the recovery…
OGGMON-20272:OGG Monitor Instance Is Unable To Register The OGG Instance Due To Null – Make DBA Life Easy
Symptoms Oracle Goldengate monitor instance is not able to register with the Jagent with the below error: [2025-09-23T17:11:01.063+10:00] [JAGENT] [ERROR] [OGGMON-20272] [com.goldengate.monitor.jagent.jmx.MBeansContainerImpl] [tid: StatusCollector] [ecid: 0000P_WUnVbF^64_zTO5yW1cnElJ000003,0] unable to register the OGG instance. Due to null[2025-09-23T17:11:11.063+10:00] [JAGENT] [ERROR] [OGGMON-20498] [com.goldengate.monitor.jagent.comm.ws.NotificationsCollector] [tid: StatusCollector] [ecid: 0000P_WUnVbF^64_zTO5yW1cnElJ000003,0] Exception thrown is: [[java.lang.NullPointerException at com.goldengate.monitor.jagent.comm.impl.ManagerFacadeImpl.getCachedInstances(ManagerFacadeImpl.java:299) at com.goldengate.monitor.jagent.comm.ws.NotificationsCollector.checkAndDeleteProcess(NotificationsCollector.java:162) at com.goldengate.monitor.jagent.comm.ws.NotificationsCollector.checkAndPublishRegisteredAndDeletedProcess(NotificationsCollector.java:150) at com.goldengate.monitor.jagent.comm.ws.NotificationsCollector.run(NotificationsCollector.java:80)…
RMAN-06004: Oracle error from recovery catalog database: RMAN-20020: database incarnation not set – Make DBA Life Easy
The following errors occur when RMAN backup took place: RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of backup command at 08/06/2025 12:38:27RMAN-03014: implicit resync of recovery catalog failedRMAN-06004: Oracle error from recovery catalog database: RMAN-20020: database incarnation not set RESOLUTION RMAN> reset database;reset database;database incarnation already registered
ASM Instance ORA-04031: unable to allocate 232 bytes of shared memory (“shared pool”,”select ks.inst_id,ksuxsins,k…”,”KKSSP^2431″,”kglpn”) – Make DBA Life Easy
One of the +ASM instance alert log has ORA-04031 errors: 2025-07-17T23:00:48.475567+10:00Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_ppa7_64031.trc (incident=160513):ORA-04031: unable to allocate 2072 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","parameter table block")Incident details in: /u01/app/grid/diag/asm/+asm/+ASM1/incident/incdir_160513/+ASM1_ppa7_64031_i160513.trcUse ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.2025-07-17T23:00:48.550908+10:00ORA-04031 heap dump being written…
How to add a port to a service in firewalld – Make DBA Life Easy
This post shows how to add port 5000 into filrewalld for Oracle Autonomous Health Framework (AHF) running on GI/RAC. List existing services:# firewall-cmd --get-services Add the port: If the service you want to modify already exists, use the following command to add the port: # firewall-cmd --permanent --add-port=<port>/<protocol> --service=<service-name>eg. #firewall-cmd --permanent --add-port=5000/tcp --service=oracle_rac Reload firewalld: After making changes,…
Clean Grid Infrastructure Environment – Make DBA Life Easy
The purpose of this document is to describe steps to clean clusterware (GI) environment where deinstall fails to work or cannot complete. Cleaning Steps 1. Remove the current GI home from central inventoryAs grid home owner: $ORACLE_HOME/oui/bin/runInstaller -detachHome -silent -local ORACLE_HOME=$ORACLE_HOME 2. Disable oracle ohasd service on Linux as root user # /usr/bin/systemctl stop oracle-ohasd.service#…
“runcluvfy.sh Stage -pre crsinst -n node1,node2” Hangs – Make DBA Life Easy
The following command hangs : $cd /u01/app/19.0.0/grid/$./runcluvfy.sh Stage -pre crsinst -n node1,node2 -verbose SOLUTION $ cat /etc/redhat-releaseRed Hat Enterprise Linux release 8.6 (Ootpa) As workaround: $export CV_ASSUME_DISTID=OEL7.8$./runcluvfy.sh stage -pre crsinst -n node1,node2 -verboseOrUse the latest Cluster Verification Utility (CVU) (Doc ID 2731675.1) Patch 30839369
How to Switch or Recreate Undo Tablespace at PDB Level – Make DBA Life Easy
From 12.2.0.1 onwards, local undo tablespace is introduced in PDB level. In many cases, the automatically created undo tablespace in PDB is not complying with naming standard. For example: SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id=3 order by 1; INST_ID NAME CON_ID VALUE---------- -------------------- ---------- -------------------- 1 undo_tablespace 3 UNDOTBS1 2…
Local and Shared Undo Tablespace Modes – Make DBA Life Easy
Oracle introduced Local Undo since 12cR2. Before 12cR2, there is no local undo concept, the Undo tablespace used to be shared or global across all the PDBs in a container. How to check the current Undo mode SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';PROPERTY_NAME PROPERTY_V DESCRIPTION------------------------- ---------- ------------------------------LOCAL_UNDO_ENABLED TRUE true if local undo is enabled…
Oracle RAC Database Private Thread – Make DBA Life Easy
One of our RAC database has a private thread for some reason by a DBA playing around. SQL> select thread#, enabled from v$thread; THREAD# ENABLED---------- -------- 1 PUBLIC 2 PUBLIC 3 PRIVATE SOLUTION Check spfile with thread 3 included. Add redo log groups for thread 3. Add undo tablespace for threda 3. Disable thread 3.SQL>…
RMAN-06004: Oracle error from recovery catalog database: RMAN-20005: target database name is ambiguous – Make DBA Life Easy
The following errors occur in the trace file when duplicate a database: MAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of Duplicate Db command at 05/13/2025 21:01:07RMAN-05501: aborting duplication of target databaseRMAN-06004: Oracle error from recovery catalog database: RMAN-20005: target database name is ambiguousDBGMISC: ENTERED krmkursr [21:01:13.976]DBGMISC: EXITED krmkursr [21:01:13.976] elapsed time [00:00:00:00.000]-------------------------------------------------------------------------------Trace…
How to Discover Autonomous Database in Oracle Enterprise Manager (OEM) – Make DBA Life Easy
This post demonstrate how to discover your Autonomous Database in OEM. Here EM Agent on OMS (Oracle Management Service ) is used to remotely monitor the Autonomous Database ( ADB ). Prerequisites 1. In this example an ADW will be discovered and monitored by the on-premise OEM by using OMS central agent for convenience . 2. Connect…
How to find the PID of the process using a specific port? – Make DBA Life Easy
# lsof -i :9933COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEoracle_51 51639 oracle 6u IPv4 3233899771 0t0 UDP localhost.localdomain:9933 # netstat -nlp|grep 9933udp 1053952 0 127.0.0.1:9933 0.0.0.0:* 51639/oracleTESTDB # fuser -v -n udp 9933 USER PID ACCESS COMMAND9933/udp: oracle 51639 F.... oracle_51639_TESTDB Check Session id(SID) and SQL statement from OS process id(PID) in Oracle…
EM 13c: How to Change Monitoring Credentials For WebLogic Domain Target – Make DBA Life Easy
We changed WebLogic user password for enforcing security principal, then WebLogic domain targets show down on OEM. Hence we need update the monitoring credentials on OEM for WebLogic domain. 1) Verify the correct Admin Server hostname and password are correct: EM Console > Middleware Targets > click the WebLogic Domain > WebLogic Domain dropdown menu…
Oracle Duplicate a Database from a Standby Database – Make DBA Life Easy
This post demonstrates how to duplicate an Oracle database from a standby database instead of from a primary database normally. The environment list: Primaey DBStandby DBDulicate DBTESTDBSTBYDBDUDB Assume that all tnsnames are already configured. Password file, spfile are also already configured as the following: Copy the password file from source database for the target database,…
Oracle Data Guard Most Used Commands – Make DBA Life Easy
Note: Static "_DGMGRL" entries are no longer needed as of Oracle Database 12.1.0.2 and later release in Oracle Data Guard Broker configurations that are managed by Oracle Restart, RAC On Node or RAC as the Broker will use the clusterware to restart an instance. ( Oracle Data Guard Broker and Static Service Registration (Doc ID…
OEM Generic Alert Log Error With Message “Dumping Current Patch Information” – Make DBA Life Easy
Database alert log shows entry of patch description injected in alert log (this is a RDBMS feature which is introduced) Solution From RDBMS side: Set below parameters defined at the spfile or pfile level in the database, to avoid dumping the patch information in alert log. -- To avoid dumping the parameters information_log_segment_dump_parameter=FALSETo avoid dumping the…
OPW-00021: Failed to retrieve DB password file location from the CRS resource – Make DBA Life Easy
When create Oracle password file in ASM, the following errors occur: $ orapwd file='+DATA1/TESTDB/PASSWORD/pwdTESTDB' dbuniquename='TESTDB'Enter password for SYS:OPW-00021: Failed to retrieve DB password file location from the CRS resource RESOLUTION Add database and instances into CRS by running 'srvctl', but do not specify "-pwfile '+DATA1/TESTDB/PASSWORD/pwdTESTDB': $ srvctl add database -d TESTDB -oraclehome /u01/app/oracle/product/19.0.0/dbhome_1 -dbtype RAC…
Physical Standby Data Guard Health Check – Make DBA Life Easy
Check N+1 standby redo logs are created on both primary and standby -- on primary SQL> SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#; THREAD# GROUP# SEQUENCE# BYTES ARC STATUS---------- ---------- ---------- ---------- --- ---------- 1 11 0 1073741824 YES UNASSIGNED 1 12 0 1073741824 YES UNASSIGNED 1 13 0…
Convert RAC Physical Standby To Snapshot Standby – Make DBA Life Easy
Note: For snapshot standby, you do not need Flashback enabled at database level Convert physical standby to snapshot standby On Standby Database SQL> select name, open_mode from v$database;NAME OPEN_MODE--------- -----------TESTDB MOUNTEDSQL> alter database convert to snapshot standby;alter database convert to snapshot standby*ERROR at line 1:ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_02/28/202521:40:27'.ORA-01153: an incompatible media recovery is…
No Stats, Stale Stats and Locked Stats in Oracle Objects – Make DBA Life Easy
N0 Stats SQL> SELECT OWNER, TABLE_NAME from dba_tables where last_analyzed is null;SQL> SELECT OWNER, TABLE_NAME from dba_indexes where last_analyzed is null;SQL> select count(*) from dba_ind_statistics where LAST_ANALYZED is null; COUNT(*)---------- 795SQL> select count(*) from dba_tab_statistics where LAST_ANALYZED is null; COUNT(*)---------- 406 Stale Stats SQL> SELECT OWNER, TABLE_NAME, STALE_STATS FROM DBA_TAB_STATISTICS WHERE STALE_STATS='YES' ;SQL> SELECT OWNER,…
Oracle ASM Thin Provisioned Attribute and Reclaim Storage Space – Make DBA Life Easy
ASMFD Installation & Configuration Verify the ASM Filer Driver (AFD) Kernel module has been loaded. $ lsmod | grep afdoracleafd 226702 1 To confirm ASM Filter Driver has already been installed, loaded, is supported and versions, use afddriverstate installed | loaded | supported | version. $ afddriverstate installedAFD-9203: AFD device driver installed status: 'true'$ afddriverstate loadedAFD-9205:…
RMAN-06004: Oracle error from recovery catalog database: RMAN-20005: target database name is ambiguous – Make DBA Life Easy
When trying to duplicate a database, the following RMAN errors occur: RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of Duplicate Db command at 01/29/2025 16:01:38RMAN-05501: aborting duplication of target databaseRMAN-06004: Oracle error from recovery catalog database: RMAN-20005: target database name is ambiguous RESOLUTION RMAN> SET DBID=123456789;RMAN> DUPLICATE DATABASE....
Create a 19c RAC Standby Database From Overnight RMAN Tape Backups – Make DBA Life Easy
Here is an example of building a RAC standby database by using overnight NetBackup RMAN backups. Primary RAC servers: racnode1,racnode2Standby RAC servers: stdbynode1,stdbynode2Primary Database Name: RACTESTPrimary Instance Names: RACTEST1/2Standby Instance Names: STBTEST1/2RAC Database version: 19.x.x.x.x 1)On primary database, check most recent RMAN backups are available. $ rman target / catalog rman/password@rman RMAN> list backup of…
13c OEM Information Publisher Reports: Error rendering element. Exception: ORA-00942: table or view does not exist – Make DBA Life Easy
When using the OEM Reporting System you are limited to access the repository only (MGMT_VIEW). So, the solution is to grant select on schema.table_name to MGMT_VIEW. You need give a read permission to MGMT_VIEW. grant select on sysman.TABLE_NAME to MGMT_VIEW; This will help you remove the error while reporting the data from OEM Information publisher.
ORA-01157 When Standby Database Open Read Only – Make DBA Life Easy
The following errors occur when trying to open a standby database read only: SQL> alter database open read only;alter database open read only*ERROR at line 1:ORA-10458: standby database requires recoveryORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: '+DATA2/TESTDB/DATAFILE/system.3203.1186181325' When checking ASM file for SYSTEM tablespace, the file exists but…
RMAN-06023 Although Backups Are Available – Make DBA Life Easy
'RMAN RESTORE DATABASE' command fails with the following errors by using a backup control file: RMAN-00571: ==================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ======== RMAN-00571: ==================================================== RMAN-03002: failure of restore command at 21/11/2024 11:09:9 RMAN-06026: some targets not found - aborting restore ....RMAN-06023: no backup or copy of datafile 3 found to restore RMAN-06023: no…
Opatch or Opatchauto takes long time Due to High Number of Inactive Patches – Make DBA Life Easy
"opatch lsinventory -inactive" shows many inactive patches.Large number of inactive patches can slow down the opatch apply process.Issue still reproduce after executing "opatch util deleteinactivepatches" in this case opatch.properties:retain was set to retain=2.There is also known issue with certain oracle homes where there are more than 1 inactive PSU or RU, a user needs to…
“ORA-16642: DB_UNIQUE_NAME mismatch” while adding a Standby Database into DG Broker – Make DBA Life Easy
The following error occurs while trying to add standby database into data guard broker. DGMGRL> ADD DATABASE TESTDB AS CONNECT IDENTIFIER is TESTDB_DR MAINTAINED AS PHYSICAL;Error: ORA-16642: DB_UNIQUE_NAME mismatch REASON Here primary and standby database DB_UNIQUE_NAME is same as "TESTDB". Make sure DB_UNIQUE_NAME for primary and all standby databases are different.
How to Manually Apply OCW Release Update onto Database Home – Make DBA Life Easy
Found OCW patch was not applied onto database home after applied 19.24 RU, so manual applying is required. $ORACLE_HOME/OPatch/opatch lspatches......30159782;OCW Interim patch for 30159782 For 19.24, the OCW patch id should be "36587798;OCW RELEASE UPDATE 19.24.0.0.0 (36587798)". Let's manually apply it. $ cd 36582629/36587798$ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch applyOracle Interim Patch Installer version 12.2.0.1.44Copyright (c) 2024, Oracle Corporation.…
How to Connect Oracle GoldenGate Veridata Agent to Autonomous Data Warehouse – Make DBA Life Easy
This post demonstrate how to connect the Oracle GoldenGate Veridata to Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing (ATP) by using the Oracle Wallet. Downloaded the Wallet_ADWTESTDB.zip from the ADW/ATP console. Unzip the Wallet in the location $ unzip Wallet_ADWTESTDB.zipArchive: Wallet_ADWTESTDB.zipinflating: cwallet.ssoinflating: tnsnames.orainflating: truststore.jksinflating: ojdbc.propertiesinflating: sqlnet.orainflating: ewallet.p12inflating: keystore.jks Add the following into sqlnet.ora WALLET_LOCATION…
GI Postpatch Command Fails Due To rpm Database Corruption At The OS Level – Make DBA Life Easy
The following errors occur when running "$ORACLE_HOME/crs/install/rootcrs.sh -postpatch": 2024-11-09 13:31:20: Executing cmd: /bin/rpm -q sles-release 2024-11-09 13:31:20: Command output: > error: rpmdb: BDB0113 Thread/process 128527/140400711669824 failed: BDB1507 Thread died in Berkeley DB library > error: db5 error(-30973) from dbenv->failchk: BDB0087 DB_RUNRECOVERY: Fatal error, run database recovery > error: cannot open Packages index using db5 -…
Installing a 13.5 Management Agent Using the agentDeploy Script – Make DBA Life Easy
We can install a Management Agent using the agentDeploy.sh script in the following ways: Using EM CLI from the Remote Destination Host Using EM CLI from the OMS Host Using EM CLI from the remote Destination Host Acquiring the Management Agent Software and Downloading it onto the destination Host Using EM CLI. Download and Set…
How to Connect to an Oracle Autonomous Database – Make DBA Life Easy
Connecting to an Autonomous Database using SQL Developer Firstly download a wallet for an Autonomous Database. Overview>Autonomous Database>Autonomous Database details>Database connection>Wallet type ( Instance wallet )>Download by giving password. Then open SQL Developer on your local computer. In the Connections panel, right-click Connections and select New Connection. Connecting to an Autonomous Database using tnsnames.ora Copy and…