【学习】
2020年阅读清单
-
Oracle session reset parameter Tips
Oracle session reset parameter Tips
session reset parameter ORA-00922/ORA-03001
11g:
SYS@orcl> alter session reset "_small_table_threshold"; alter session reset "_small_table_threshold" * ERROR at line 1: ORA-00922: missing or invalid option SYS@orcl>12c/19c:
HONG@pdb1> alter session reset "_small_table_threshold"; ERROR: ORA-03001: unimplemented feature HONG@pdb1>12.2 database, alter session reset failed with ORA-03001. (alter system reset didn’t run into this problem).
Fix:
Run alter session set to revert init parameter value backReference
12.2 Alter Session Reset Failed With ORA-03001: unimplemented feature (Doc ID 2548082.1)Have a good work&life! 2021/10 via LinHong
-
Oracle Sample Schema Tips
Oracle Sample Schema Tips
-
Oracle Calibrate IO Test Tips
Oracle Calibrate IO Test Tips
Oracle Calibrate IO
SET SERVEROUTPUT ON DECLARE lat INTEGER; iops INTEGER; mbps INTEGER; BEGIN -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat); DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 20, iops, mbps, lat); DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); dbms_output.put_line('max_mbps = ' || mbps); end; / select start_time, max_iops, max_mbps, max_pmbps,latency,num_physical_disks from DBA_RSRC_IO_CALIBRATE; ---- TEST SYS@cdb1> SET SERVEROUTPUT ON SYS@cdb1> DECLARE 2 lat INTEGER; 3 iops INTEGER; mbps INTEGER; 5 BEGIN 6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat); 7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 20, iops, mbps, lat); 8 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); 9 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); 10 dbms_output.put_line('max_mbps = ' || mbps); end; 12 / max_iops = 1829 latency = .001 max_mbps = 367 max_iops = 1829 latency = 0 max_mbps = 367 PL/SQL procedure successfully completed. SYS@cdb1> SYS@cdb1> select start_time, max_iops, max_mbps, max_pmbps,latency,num_physical_disks from DBA_RSRC_IO_CALIBRATE; START_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS ---------------------------- --------- --------- ---------- ------------ ------------------- 08-OCT-21 04.24.06.317964 PM 1829 367 346 .001 2 SYS@cdb1>Items Type Comments num_physical_disks IN Approximate number of physical disks in the database storage max_latency IN Maximum tolerable latency in milliseconds for database-block-sized IO requests max_iops OUT Maximum number of I/O requests per second that can be sustained. The I/O requests max_mbps OUT Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O requests are randomly-distributed, 1 megabyte reads. actual_latency OUT Average latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds
-
Oracle ORA-01552 Tips
Oracle ORA-01552 Tips
-
Oracle Monitor wrong password user Tips
Monitor wrong password user
Trigger to get ORA-1017 error and write messages to alert log
CREATE OR REPLACE TRIGGER logon_denied_write_alertlog AFTER SERVERERROR ON DATABASE DECLARE l_message varchar2(2000); BEGIN -- ORA-1017: invalid username/password; logon denied IF (IS_SERVERERROR(1017)) THEN select 'Failed login attempt to the "'|| sys_context('USERENV' ,'AUTHENTICATED_IDENTITY') ||'" schema' || ' using ' || sys_context ('USERENV', 'AUTHENTICATION_METHOD') ||' authentication' || ' at ' || to_char(logon_time,'yyyy/mm/dd hh24:mi:ss' ) || ' from ' || osuser ||'@'||machine ||' ['||nvl(sys_context ('USERENV', 'IP_ADDRESS'),'Unknown IP')||']' || ' via the "' ||process||'" process.' into l_message from sys.v_$session where sid = to_number(substr(dbms_session.unique_session_id,1 ,4), 'xxxx') and serial# = to_number(substr(dbms_session.unique_session_id,5 ,4), 'xxxx'); -- write to alert log sys.dbms_system .ksdwrt( 2,l_message ); END IF; END; /Test Case
User wrong password to connect.
[oracle@ol8-21c ~]$ sqlplus lin/oracle111 SQL*Plus: Release 21.0.0.0.0 - Production on Fri Sep 24 11:44:19 2021 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name:The message has been writen the following messages.
[oracle@ol8-21c ~]$ tail -2 /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/alert_cdb1.log : Failed login attempt to the "LIN" schema using PASSWORD authentication at 2021/09/24 11:44:21 from oracle@ol8-21c [Unknown IP] via the "282220" process. [oracle@ol8-21c ~]$Have a good work&life! 2021/09 via LinHong
-
Oracle Reset Linux 8 Root password Tips
Reset root password on Oracle Linux 8
Reset Tips
-
Reboot the system and access the
GRUBboot-loader menu. -
Find the line that begins
linuxand scroll to the very end of the line. -
Add the following parameter:
rd.breakto the end of this line. -
Press
Ctrl -Xto start your system. -
Shell prompt
switch_root:/# -
Type the following commands:
mount -o remount,rw /sysroot chroot /sysroot passwd -> input new password twice.- Run the following commands to relabel SELinux at reboot.
touch /.autorelabel exit exit- Reboot and login via new password.
Have a good work&life! 2021/09 via LinHong
-
-
Oracle 21c RAC Linux 8 Install Tips
Oracle 21c RAC Linux 8 Install Tips
Tim had prepared the detail install tips regarding oracle 21c RAC.
Very convenient and efficient!
I changed VM memory size 800MB/5GB/5GB and change the DB’s size to 800MB for my mac’s limited memeory.
-
Oracle nslookup Linux 8 Install Tips
Oracle nslookup Linux 8 Install Tips
Recent Posts
- Oracle session reset parameter Tips
- Oracle Sample Schema Tips
- Oracle Calibrate IO Test Tips
- Oracle ORA-01552 Tips
- Oracle Monitor wrong password user Tips
- Oracle Reset Linux 8 Root password Tips
- Oracle 21c RAC Linux 8 Install Tips
- Oracle nslookup Linux 8 Install Tips
- Oracle 19c mac os Client install Tips
- Oracle 19c TDE Tips
Categories
Tags