News
Entertainment
Science & Technology
Life
Culture & Art
Hobbies
News
Entertainment
Science & Technology
Culture & Art
Hobbies
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 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…
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,…
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…
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:…
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,…
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.
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…
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…
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 -…
OCI : How to copy/move files from Linux machine to object storage – Make DBA Life Easy
You need to go to your bucket details and click on the right side to open the tab with “Pre-Authenticated Requests” Click on “Create Pre-Authenticated Request”, choose the name and expiration time for the link. The link will appear in a pop-up window only once, and you have to copy and save it if you…
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…
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…
Installing Oracle Management Agent in Silent Mode by Using the AgentPull Script – Make DBA Life Easy
This is to demonstrate how to install 13.5 Oracle Management Agent in Silent Mode by Using the AgentPull Script . Note: To install a Management Agent using the AgentPull script, you DO NOT need to download the Management Agent software onto the destination host. The AgentPull script performs this action automatically. Get AgentPull script from…
ORA-15099: disk is larger than maximum size of 2097152 MBs – Make DBA Life Easy
The following errors occur when creating a new ASM diskgroup: SQL> CREATE DISKGROUP DATA1 EXTERNAL REDUNDANCY disk 'AFD:DATA1_DISK1' NAME DATA1_DISK1;CREATE DISKGROUP DATA1 EXTERNAL REDUNDANCY disk 'AFD:DATA1_DISK1' NAME DATA1_DISK1;*ERROR at line 1:ORA-15018: diskgroup cannot be createdORA-15099: disk 'AFD:DATA1_DISK1' is larger than maximum size of 2097152 MBs SOLUTION SQL> CREATE DISKGROUP DATA1 EXTERNAL REDUNDANCY disk 'AFD:DATA1_DISK1' NAME…
M 13c: Enterprise Manager 13c Cloud Control Agent Control Commands Respond with Message: Invalid EMSTATE valueEMD – Make DBA Life Easy
The "Invalid EMSTATE valueEMD" error occurs when reload/upload/pingOMS agent: oracle:/u01/app/oracle/product/13.5.0/agent_1/agent_inst/bin$ ./emctl reload agentOracle Enterprise Manager Cloud Control 13c Release 5Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.---------------------------------------------------------------Invalid EMSTATE valueEMD reload completed successfully Check what value is for EMSTATE : $ cat emctl #!/bin/sh -f #++ # # 16-dec-02.15:38:56 vnukal # # Copyright (c) 2002,…
ORA-15046: ASM file name ‘+DATA2/TESTDB/PASSWORD/pwdtestdb.256.1157213397’ is not in single-file creation form – Make DBA Life Easy
When relocating an ASM password file, the following errors occur: ASMCMD> pwcopy --dbuniquename TESTDB '+DATA1/TESTDB/PASSWORD/pwdtestdb.256.1157213397' '+DATA2/TESTDB/PASSWORD/' -f......ORA-15046: ASM file name '+DATA2/TESTDB/PASSWORD/pwdtestdb.256.1157213397' is not in single-file creation form...... The destination name cannot be an OMF format. so the command should be like : ASMCMD> pwcopy --dbuniquename TESTDB '+DATA1/TESTDB/PASSWORD/pwdtestdb.256.1157213397' '+DATA2/TESTDB/PASSWORD/pwdtestdb.pwfile' -f
ORA-44305: service is running ORA-44311: service not running when STOP or DELETE a Service – Make DBA Life Easy
In RAC environment, 'DBMS_SERVICE.ALL_INSTANCES' is required to stop or delete a service. Otherwise ORA-44305 or ORA-44311 will occur. SQL> exec DBMS_SERVICE.DELETE_SERVICE('TEST_SERVICE');BEGIN DBMS_SERVICE.DELETE_SERVICE('TEST_SERVICE'); END;*ERROR at line 1:ORA-44305: service TEST_SERVICE is runningORA-06512: at "SYS.DBMS_SERVICE", line 68ORA-06512: at "SYS.DBMS_SERVICE", line 458ORA-06512: at line 1SQL> exec DBMS_SERVICE.STOP_SERVICE('TEST_SERVICE');BEGIN DBMS_SERVICE.STOP_SERVICE('TEST_SERVICE'); END;*ERROR at line 1:ORA-44311: service TEST_SERVICE not runningORA-06512: at "SYS.DBMS_SYS_ERROR", line…
How to Monitor Undo Tablespace Usage and the Free Space – Make DBA Life Easy
To check the current size of the Undo tablespace: SQL> select sum(a.bytes)/1024/1024/1024 as undo_size_GB, c.tablespace_name from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO' and c.status = 'ONLINE' and b.name = c.tablespace_name and a.ts# = b.ts# group by c.tablespace_name;UNDO_SIZE_GB TABLESPACE_NAME------------ ------------------------------ 31.9990234 UNDOTBS1 31.9990234 UNDOTBS2 2. To check the free space (unallocated) space…