Category Archives: Oracle DBA Practice

Warning: standby redo logs not configured for thread 0 on DATABASE

When run “Validate Database” DGML command, get  “Warning: standby redo logs not configured for thread 0 ” and “Insufficient SRLs” warnings on 12.1.0.2 database with “Database Patch Set Update : 12.1.0.2.170814 (26609783)”. DGMGRL> connect sys Password: Connected as SYSDG. DGMGRL> validate … Continue reading

Posted in DataGuard, GI and RAC | Tagged , , , | Leave a comment

Temporary Tablespace Group

Create a temporary tablespace, and implicitly add it to a temporary tablespace group. SQL>CREATE BIGFILE TEMPORARY TABLESPACE TEMP01 TEMPFILE ‘+DATA1’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32G EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M TABLESPACE GROUP TEMP_GRP; Add a temporary … Continue reading

Posted in Configuration, SQL Scripts For DBA | Tagged , , , , , , , , | Leave a comment

Drop Temporary Tablespace Hangs With “enq: TS – contention”

After created a new temporary tablespace TEMPNEW as database default temporary tablespace, then try to drop the old temporary tablespace “TEMP”, it hangs up forever. SQL> drop tablespace temp including contents and datafiles; Check session wait event “enq: TS – … Continue reading

Posted in Configuration | Tagged , , | Leave a comment

Huge ASM LMHB trace File Size in 12cR1

The ASM LMHB trace file is huge in 12.1.0.2 GI environment. $cd /u01/app/grid/diag/asm/+asm/+ASM1/trace $ ls -ltr *lmhb* -rw-r—– 1 grid oinstall 11551946219 Jan 8 16:59 +ASM1_lmhb_88049.trc According to (Doc ID 2137683.1), it is a known bug, which is fixed in 12cR2 … Continue reading

Posted in ASM | Tagged , , | Leave a comment

RMAN Backup Failed With RMAN-03014 RMAN-03009 Errors

RMAN failed with below errors while resync the recovery catalog. … .. . starting full resync of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03015: error occurred in stored script al_netbkup RMAN-03002: failure of … Continue reading

Posted in RMAN Restore and Recovery | Tagged , , , , , , , | Leave a comment

How to Check Oracle Database Current Number of Sessions / Processes and Maximum Utilization

1)From V$LICENSE View. It shows USER sessions only excluding database system sessions. SQL> select SESSIONS_CURRENT,SESSIONS_HIGHWATER from v$license; SESSIONS_CURRENT SESSIONS_HIGHWATER —————- —————— 771 1346 — SESSIONS_CURRENT :Current number of concurrent user sessions — SESSIONS_HIGHWATER :Highest number of concurrent user sessions since the instance … Continue reading

Posted in OEM12c, OEM13c, SQL Scripts For DBA | Tagged , , , , , , , , , , , , , | Leave a comment

How to Make ASM Instance to Use HugePages

The ASM instance is configured in the same way as normal databases instance for using hugepages in Linux 6. But ASM is not using hugepages, while the normal instance is using hugepages. There are enough free hugepages available  as per … Continue reading

Posted in ASM, GI and RAC | Tagged , , , , , , , , , , , | Leave a comment

Database Size History Report From OEM

A DBA is always asked about the database size growth trend for coming months  of one database  or databases, which possibly face storage or performance issue.  We can only get current database size from the database, or the size of … Continue reading

Posted in OEM12c, OEM13c, SQL Scripts For DBA | Tagged , , | Leave a comment

TFA – Oracle Trace File Analyser Installation

Uninstall TFA 1) Check TFA_HOME. $ grep TFA_HOME= /etc/init.d/init.tfa TFA_HOME=/u01/app/12.2.0.1/grid/tfa/racnode1/tfa_home 2) Uninstall TFA as root user. [root@racnode1 bin]# ./tfactl -h Usage : /u01/app/12.2.0.1/grid/bin/tfactl <command> [options] commands:diagcollect|collection|analyze|ips|run|start|stop|enable|disable|status|print|access|purge|directory|host|receiver|set|toolstatus|uninstall|diagnosetfa For detailed help on each command use: /u01/app/12.2.0.1/grid/bin/tfactl <command> -help [root@racnode1 bin]# /u01/app/12.2.0.1/grid/bin/tfactl uninstall … Continue reading

Posted in 12c new features, Configuration | Tagged , | Leave a comment

ORMB Batch Scheduling Job Hangs At F1-FLUSH with “PL/SQL Lock Timer” Wait Event

Overnight ORMB Batch Scheduling Job has been running for more than twenty hours, user wants to know why ? 1) Check which job at what step is still running and hanging. SQL> select OWNER,JOB_NAME,JOB_SUBNAME,SESSION_ID,ELAPSED_TIME,CPU_USED from dba_scheduler_running_jobs; OWNER JOB_NAME JOB_SUBNAME SESSION_ID … Continue reading

Posted in dbms_scheduler, ORMB | Tagged , , , , , | Leave a comment