Lin Hong's TECH Blog!

【学习】 2025年阅读清单

Oracle Hugepage Tips

2020-04-14

Oracle

Oracle Hugepage Tips

Oracle Hugepage Tips

Use the hugepages_settings.sh to recommond vm.nr_hugepages.

Read All

Oracle AWR delete Tips

2020-04-07

Oracle

Oracle AWR Tips

Oracle AWR delete Tips

Check SYSAUX’s contents.
How to clear history AWR reports in sysaux tablespace?

Use the following enviroments.

[oracle@ora7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 7 16:20:24 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@orcl> !cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.6 (Maipo)

SYS@orcl>

Read All

Github SpeedUp Tips

2020-04-04

Git

Git Tips

Github SpeedUp Tips

Read All
Markdown Tips

2020-04-01

Markdown

Markdown Tips

Markdown Tips

Read All

Oracle Flash Cache Hit Tips

2020-03-31

Oracle

Oracle Flash Tips

Database Smart Flash Cache

Oracle Flash Cache Hit Tips

Database Smart Flash Cache

Ref:

Oracle Database 11g Release 2に関する10の重要なこと – askTom Live -

Point8：　Flash Cache 【Database Smart Flash Cache】

現在のところフラッシュキャッシュはOracle Enterprise LinuxとSolaris版(SPARC/x64)のOracle Database Enterprise Editionのみで使える機能です。

また、Oracle Exadataに実装されているExadata Smart Flash Cacheと混同してしまう方がいるかもしれません。
ここで説明したフラッシュキャッシュ（Database Smart Flash Cache）は、Oracle Exadataのそれとはまったく別の、独立した機能です。データベース・サーバー上でバッファキャッシュとともに利用するもので、ストレージレベルの機能ではありません。

Read All

Oracle Import Error Hit Tips

2020-03-30

Oracle

Oracle Import Tips

Oracle Import Error Hit Tips

Export dumpfile from Oracle db 12.2 and import dumpfile to Oracle 12.1.

There was the following errors in import command.

ORA-39126: KUPW$WORKER.MOVE_DATA [TABLE_DATA:"XXX"."YYYYYYY"]
ORA-39096: METHOD
ORA-06512: "SYS.DBMS_SYS_ERROR"
ORA-06512: "SYS.KUPW$WORKER",

Command eg.

Export command:

expdp <user>/<password> SCHEMAS=<schema> DIRECTORY=<dir_dump>　DUMPFIE=<dump_file> LOGFILE=<logfile_12.1.log> VERSION=12.1

Import command:

impdp <user>/<password> SCHEMAS=<schema> EXCLUDE=USER DIRECTORY=<dir_dump> DUMPFILE=<dump_file> EXCLUDE=STATISTICS LOGFILE=<logfile_12.2.log>

Reference:

Unable To Import Table Using DataPump Import (IMPDP) And Hitting ORA-39126 And ORA-39096 Errors (Doc ID 2193782.1)

To resolve the errors prior 12.2, apply Patch 20056333 and run post-installation step:

	1. Navigate to the <ORACLE_HOME>/OPatch directory:
	$ cd <ORACLE_HOME>/OPatch
	2. Install the SQL portion of the patch by running the following command:
	$ datapatch

Have a good work&life! 2020/03 via LinHong

Read All

Oracle 19c Poor Performance With DataPump Export

2020-03-24

Oracle

Oracle DataPump Tips
- Reference
Oracle 19c Poor Performance With DataPump Tips

Reference

Poor Performance With DataPump Export On Large Databases (Doc ID 473423.1)

Data Pump Export Hanging In RAC At ESTIMATE Phase And Slow Performance (Doc ID 1948926.1)
Read All
Oracle Exadata Smart Flash Cache Tips

2020-03-24

Oracle

Oracle Exadata Flash Tips
- Flash cache mode WriteThrough and WriteBack
Oracle Exadata Smart Flash Cache Tips

Flash cache mode WriteThrough and WriteBack

The default flash cache mode is WriteThrough on X4 and earlier systems.

Since April 2017, Oracle Exadata Deployment Assistant (OEDA) enables Write-Back Flash Cache by default if the following conditions are met:
1. GI and DB home must be 11.2.0.4.1 or higher 12.1.0.2 or higher 12.2.0.2 or higher AND
2. DATA diskgroup has HIGH redundancy
From Exadata Storage Server release 11.2.3.2.0, Exadata Smart Flash Cache can operate either as a Write-through cache or a Write-back cache. Write-through mode is best suited for random repeated reads commonly found in OLTP applications. Write-back mode is best suited for write-intensive applications.

Write-Through -> first write to physical disks and then to flash cache for faster reads. Write-Back -> first write to flash cache and then to physical disk for both faster reads and writes.
Read All
Oracle 19c Wait event 'enq: KO - fast object checkpoint'

2020-03-18

Oracle

Oracle Event Tips
- wait event: ‘enq: KO - fast object checkpoint’
Oracle 19c Wait event ‘enq: KO - fast object checkpoint’

There were top event - ‘enq: KO - fast object checkpoint’ while I was doing awr report analysis, and found some test with it and some mos.

wait event: ‘enq: KO - fast object checkpoint’

From mos: Enq: KO - Fast Object Checkpoint Wait Event (Doc ID 2547319.1)
```
On reporting queries, there are too many "enq: KO - fast object checkpoint" wait event.

All reporting queries are doing full table scans concurrently. Oracle uses direct path reads instead of db file scattered reads. And direct path reads require a checkpoint

"_serial_direct_read"=NEVER
```
Read All
Oracle 19c load awr report

2020-03-17

Oracle

Oracle awr Tips

Oracle 19c load awr report

Tips:

Load data from datapump file created by awrload.sql.

Use awrextr.sql extract data from AWR into a datapump file.

Use same db version is good choise(the awr format are different from db versions.)

Need to check tablespace’s size while load awr data due to the limit of tablespace.

Read All

Oracle Recover Tips

2020-03-17

Oracle

Oracle Recover Tips

1.Recover oracle password file

Oracle Recover Tips

Tips:

1.Recover oracle password file

Wrong msg:

ERROR:
ORA-01017: invalid username/password; logon denied

Solution:

Use orapwd command to recover the password file.

orapwd file=$ORACLE_HOME/dbs/orapwORCL password=Welcome1 force=y  // force=y (if the password file is existed, will replace the password file instead by force)

Read All

Oracle 19c Analysis SQL in AWR report Tips

2020-02-12

Oracle

Oracle SQLloader Tips
Oracle 19c Analysis SQL in AWR report

There are some sql in SQL ordered by CPU Time AWR report.

These sql were using so many cpu time…
```
/* SQL Analyze(0) */ select /*+ full(t) parallel(t, 6) parallel_index(t, 6) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */to_char...
 
```
What are these sqls? what are they doing?
Read All

15/37

Database Smart Flash Cache

Reference

Flash cache mode WriteThrough and WriteBack

wait event: ‘enq: KO - fast object checkpoint’

1.Recover oracle password file