News
Entertainment
Science & Technology
Life
Culture & Art
Hobbies
News
Entertainment
Science & Technology
Culture & Art
Hobbies
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…
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…
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,…
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…
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…
ORA-25152: TEMPFILE cannot be dropped at this time – Make DBA Life Easy
Check whether the tempfile is being used by users. SQL> show parameter db_filesSQL> SELECT file#, name from v$tempfile;SQL> SELECT s.sid, s.serial#, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocksFROM v$session s, v$sort_usage uWHERE s.saddr=u.session_addrORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks; SQL> select sid, username, osuser, program, machine from v$session where sid in (<sid returned from above query>);…
OEM BI Publisher : SQL query time exceeds the limit (600 sec). Stopped processing – Make DBA Life Easy
Waiting Time: 0.7 secondsData Fetch Time: 9.8 minutesTotal Execution Time: 9.9 minutesJob Priority: NormalWaiting Time: 0.7 secondsData Fetch Time: 9.8 minutesTotal Execution Time: 9.9 minutesJob Priority: NormalSQL query time exceeds the limit (600 sec). Stopped processing.[INSTANCE_ID=bip.BIP3] WORKAROUND Tunning the SQL query OR Administration -> Runtime Configuration -> Properties -> SQL Query Timeout. Change it from…
Create CDB Error: Signature of package “SYS.DBMS_BACKUP_RESTORE” has been changed – Make DBA Life Easy
The following errors occur when creating a CDB database with patch level 19.23. ERROR=RMAN-00571: =======================================================ERROR=RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ERROR=RMAN-00571: ============================================ERROR=ORA-04062: signature of package "SYS.DBMS_BACKUP_RESTORE" has been changed SEVERE: [FATAL] Error while restoring PDB backup pieceoracle.sysman.assistants.util.step.StepExecutionException: Error while restoring PDB backup piece WORKAROUND As a workaround, by selecting the "Custom database" option instead of…
PL/SQL package SYS.DBMS_BACKUP_RESTORE version PL/SQL package SYS.DBMS_RCVMAN version in TARGET database is not current – Make DBA Life Easy
Try to connect to RMAN catalog with the following errors: PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.20.00.00 in TARGET database is not currentPL/SQL package SYS.DBMS_RCVMAN version 19.20.00.00 in TARGET database is not currentconnected to target database: TESTDB (DBID=1231118758)connected to recovery catalog database CAUSE Once the DBRU/DBRUR is applied datapatch was not run. While DBRU/DBRUR will update the…
EM GC_Domain OHS Target Alerts with Error: “The Percentage Of Requests That Resulted In Errors Is X%” – Make DBA Life Easy
The OHS log at <GC_INST>/user_projects/domains/GCDomain/servers/ohs1/logs/ohs1.log shows messages like the following: [2018-08-07T10:37:08.5618-05:00] [OHS] [ERROR:32] [] [core.c] [client_id: <client ip address>] [host_id: <OMS hostname>] [host_addr: <OMS IP address>] [tid: 140073893857024] [user: oracle] [ecid: 0000MIjGpMU3b6M5mNT4iZ1RHes^00000F] [rid: 0:5701] [VirtualHost: <OMS hostname>:0] client denied by server configuration: <PATH TO $GC_INST>/WebTierIH1/config/OHS/ohs1/htdocs/empbs Cause One of the agents was trying to upload data to…
ORA-00054: resource busy and acquire with NOWAIT – Make DBA Life Easy
To check which session is holding the object: select a.inst_id, sid, serial#, username, machine from gv$session a, gv$locked_object b, dba_objects cwhere b.object_id = c.object_idand a.sid = b.session_idand a.inst_id=b.inst_idand OBJECT_NAME='TABLENAME';INST_ID SID SERIAL# USERNAME MACHINE OWNER OBJECT_NAME ------- ----- ------- ---------- ----------- ---------- ------------- 2 3365 42069 SYS TESTSERVER TESTUSER TABLENAME Kill the session, then the error…
ORA-28003: password verification for the specified password failed – Make DBA Life Easy
The following error occurs when trying to change user password in Oracle Database. SQL> alter user TESTUSER identified by "TestPassword";alter user TESTUSER identified by "TestPassword";*ERROR at line 1:ORA-28003: password verification for the specified password failed Check user profile: SQL> select profile from dba_users where username='TESTUSER';PROFILE------------------------------------------------------TEST_USER_PROFILE Find password verify function SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT FROM DBA_PROFILES WHERE…
How to Restore a PDB into another CDB from Tape Backups – Make DBA Life Easy
This is to demonstrate how to restore a pdb from backups, then plug into a different cdb with same or another pdb name. Environment Source database name CDBDBA , pdb name pdba Target database name CDBDBB, pdb name pdbb Prepare Auxiliary Database create pfile from target db SQL> create file='/tmp/initCDBDUP1.ora' from spfile; File created. drop…
How to Skip Long Running Transaction in Oracle Goldengate using SKIPTRANS – Make DBA Life Easy
We always see the similar WARNING in ggserr.log: 2024-07-14T20:33:15.697+1000 WARNING OGG-01027 Oracle GoldenGate Capture for Oracle, EXTDB.prm: Long Running Transaction: XID 3708191792.181.17.473582, Items 0, Extract EXTDB, Redo Thread 2, SCN 1447.4108855138 (6218926532450), Redo Seq #38608, Redo RBA 88457232. Check the transaction in GGSCI: GGSCI > send extract EXTDB, showtrans OR show transactions lasting for more…
OGG: Not Able To Register Extract After DB Refresh – Make DBA Life Easy
After database is refreshed, the following errors occur: GGSCI> register extract DTCAP database2024-05-16 01:12:23 ERROR OGG-08221 Cannot register or unregister EXTRACT DTCAP because of the following SQL error: OCI Error ORA (status = 26668-ORA-26668: GoldenGate process OGG$DTCAP existsORA-06512: at "SYS.DBMS_XSTREAM_GG_ADM", line 145ORA-06512: at "SYS.DBMS_XSTREAM_GG_ADM", line 186ORA-06512: at line 1). This is because dictionary tables were…
How to Create BI Publisher Report in Oracle OEM – Make DBA Life Easy
Create a Database Connection Go to BI Publisher -> Administration->JDBC Connection Create a Data Model New->Data Model -> Upper Left '+' icon -> SQL Query -> Data -> View -> Table View -> Save as Sample Data -> Top Right Save Data Model Create a Report Select Blank( Portrait ) -> Text Item ( for…
How to Change Global_Name for a PDB after Added Default Domain – Make DBA Life Easy
For some reason, a database has to be reconfigured with a domain name ".world". After change the spfile and bounced the database, we need change the global_name manually. Check current global_name in CDB: SQL> select * from global_name;GLOBAL_NAME--------------TESTDB 2. Drop database links now, and need recreate them later. because they are inaccessible after global_name changed.…
How to Drop an Orphan LOB in Oracle Database – Make DBA Life Easy
In Oracle 11.2.0.3 database, after dropped some tables, there are still lob objects in dba_objects and dba_segments. Find the table name the lob belongs to: SQL> show parameter recyclebinNAME TYPE VALUE------------------------------------ ----------- -----------------------recyclebin string on SQL> select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name) as column_name from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$…