Friday, April 29, 2022
EM 12c/13c Configure Enterprise Manager with custom or third-party SSL Certificates
Thursday, March 31, 2022
Script - Enable Customized Password Complexity in Oracle non-cdb or cdb environment
-------------------------------------------------- define S_VERIFY_FUNCTION='dbaplus_verify_function' define S_PROFILE='DEFAULT' define S_PASSWORD_LIFE_TIME='unlimited' define S_PASSWORD_GRACE_TIME='7' define S_PASSWORD_REUSE_TIME='1' define S_PASSWORD_REUSE_MAX='10' define S_PASSWORD_LOCK_TIME='.0104' define S_FAILED_LOGIN_ATTEMPTS='5' define S_NO_OF_CHAR='12' define S_NO_OF_DIFF='1' define S_NO_OF_UPPERCASE='1' define S_NO_OF_LOWERCASE='1' define S_NO_OF_DIGIT='1' define S_NO_OF_SPECIAL='1' -------------------------------------------------- set echo off set verify off set feedback off set trimspool on set serveroutput on declare isRAC varchar2(5); isContainer varchar2(5); i_return integer; i_handle integer; s_container varchar2(255); s_open_mode varchar2(255); -- SQL to grant execute privilege on the function to public s_grant varchar2(100) := 'grant execute on &S_VERIFY_FUNCTION to public'; s_close_seed varchar2(255) := 'alter pluggable database PDB$SEED close immediate'; s_open_seed varchar2(255) := 'alter pluggable database PDB$SEED open read only'; s_open_rw_seed varchar2(255) := 'alter pluggable database PDB$SEED open read write'; -- SQL to alter profile s_modify_profile varchar2(2000):=q'[ alter profile &S_PROFILE limit password_life_time &S_PASSWORD_LIFE_TIME password_grace_time &S_PASSWORD_GRACE_TIME password_reuse_time &S_PASSWORD_REUSE_TIME password_reuse_max &S_PASSWORD_REUSE_MAX password_lock_time &S_PASSWORD_LOCK_TIME failed_login_attempts &S_FAILED_LOGIN_ATTEMPTS password_verify_function &S_VERIFY_FUNCTION]'; -- PL/SQL block to create custom password verify function s_create_func varchar2(2000):=q'[ create or replace function &S_VERIFY_FUNCTION( username varchar2, password varchar2, old_password varchar2) return boolean is p_diff integer; p_lang varchar2(512); p_message varchar2(512); p_return number; begin -- Get the context p_lang and use utl_lms for p_messages p_lang := sys_context('userenv','lang'); p_lang := substr(p_lang,1,instr(p_lang,'_')-1); --19c ora_complexity_check(password varchar2, chars number, letter number, uppercase number, lowercase number, digit number, special number) --12c ora_complexity_check(password varchar2, chars number, letter number, upper number, lower number, digit number, special number) if not ora_complexity_check(password, &S_NO_OF_CHAR,null,&S_NO_OF_UPPERCASE,&S_NO_OF_LOWERCASE,&S_NO_OF_DIGIT,&S_NO_OF_SPECIAL) then return(false); end if; -- Check if the password differs from the previous password by at least S_NO_OF_DIFF characters if old_password is not null then p_diff := ora_string_distance(old_password, password); if p_diff < &S_NO_OF_DIFF then p_return := utl_lms.get_message(28211, 'RDBMS', 'ORA', p_lang, p_message); raise_application_error(-20000, utl_lms.format_message(p_message, '&S_NO_OF_DIFF')); end if; end if; return(true); end;]'; begin -- CDB select value into isContainer from v$parameter where name='enable_pluggable_database'; -- RAC database select value into isRAC from v$parameter where name='cluster_database'; if isRAC='TRUE' then s_close_seed := s_close_seed||' instances=all'; s_open_seed := s_open_seed||' instances=all'; end if; s_container := sys_context('userenv','con_name'); if s_container<>'CDB$ROOT' then -- In Non-CDB or PDB execute immediate s_create_func; execute immediate s_grant; execute immediate s_modify_profile; dbms_output.new_line; if isContainer='TRUE' then dbms_output.put_line('Succeeded in pluggable database '''||s_container||''''); else dbms_output.put_line('Succeeded in non-cdb database '''||s_container||''''); end if; else -- In root container CDB$ROOT execute immediate s_create_func; execute immediate s_grant; execute immediate s_modify_profile; dbms_output.new_line; dbms_output.put_line('Succeeded in root container '''||s_container||'''.'); begin i_handle := dbms_sql.open_cursor; --for c1 in (select name,open_mode from v$pdbs where open_mode='READ WRITE' or (name='PDB$SEED' and open_mode='READ ONLY')) for c1 in (select name,open_mode from v$pdbs) loop s_open_mode := c1.open_mode; if c1.name='PDB$SEED' and c1.open_mode='READ ONLY' then execute immediate s_close_seed; execute immediate s_open_rw_seed; s_open_mode := 'READ WRITE'; end if; if s_open_mode='READ WRITE' then dbms_sql.parse(c=>i_handle, statement=>s_create_func, language_flag=>DBMS_SQL.NATIVE, container=>c1.name); i_return := dbms_sql.execute(i_handle); dbms_sql.parse(c=>i_handle, statement=>s_grant, language_flag=>DBMS_SQL.NATIVE, container=>c1.name); i_return := dbms_sql.execute(i_handle); dbms_sql.parse(c=>i_handle, statement=>s_modify_profile, language_flag=>DBMS_SQL.NATIVE, container=>c1.name); i_return := dbms_sql.execute(i_handle); dbms_output.new_line; dbms_output.put_line('Succeeded in pluggable database '''||c1.name||'''.'); if c1.name='PDB$SEED' then execute immediate s_close_seed; execute immediate s_open_seed; end if; else dbms_output.new_line; dbms_output.put_line('Skipped pluggable database '''||c1.name||''' which is in '''||s_open_mode||''' mode.'); end if; end loop; dbms_sql.close_cursor(i_handle); exception when others then dbms_sql.close_cursor(i_handle); raise; end; end if; dbms_output.new_line; end; /
Friday, March 11, 2022
Export/import APEX workspace and application with SQLcl
alter session set current_schema=APEX_190100;
apex_190100@ddb01> select workspace_id,workspace from apex_workspaces; WORKSPACE_ID WORKSPACE ---------------- -------------------------- 11 COM.ORACLE.APEX.REPOSITORY 12 COM.ORACLE.CUST.REPOSITORY 10 INTERNAL 4482715458638925 SALES 2104419987831886 FINANCE 6822807081190901 HR
apex_190100@ddb01> apex export -workspaceid 4482715458638925 -expWorkspace Exporting Workspace 4482715458638925:'SALES' Completed at Thu Mar 10 19:56:28 EST 2022
apex_190100@db02> @w4482715458638925.sql API Last Extended:20190331 Your Current Version:20190331 This import is compatible with version: 20190331 COMPATIBLE (You should be able to run this import without issues.) WORKSPACE 4482715458638925 Creating workspace SALES... Creating Groups... Creating group grants... Creating Users... ...workspace objects ...RESTful Services SCHEMA SALESDATA - User Interface Defaults, Table Defaults SCHEMA HRDATA - User Interface Defaults, Table Defaults User Interface Defaults, Attribute Dictionary ... elapsed: 14.1 sec ...done
apex_190100@db01> apex export -workspaceid 4482715458638925 Exporting Application 108:'Promotion Channel' Completed at Fri Mar 11 21:23:27 EST 2022 Exporting Application 126:'Sales History' Completed at Fri Mar 11 21:23:27 EST 2022 Exporting Application 128:'Customers' Completed at Fri Mar 11 21:23:27 EST 2022
apex_190100@db02> @f126.sql API Last Extended:20190331 Your Current Version:20190331 This import is compatible with version: 20190331 COMPATIBLE (You should be able to run this import without issues.) APPLICATION 126 - Sales History --application/delete_application --application/create_application --application/shared_components/navigation/lists/data_load_process_train_load_customers --application/shared_components/navigation/lists/admin_reports --application/shared_components/navigation/lists/order_page --application/shared_components/navigation/lists/mobile_options --application/shared_components/navigation/lists/footer_controls --application/shared_components/navigation/lists/charts --application/shared_components/navigation/lists/data_load_process_train_load_customers_002 --application/shared_components/navigation/lists/reports ...<output truncated> ... --application/deployment/checks --application/deployment/buildoptions --application/end_environment ... elapsed: 8.32 sec ...done
Monday, March 7, 2022
Database failed to start with errors ORA-01565 ORA-17503 ORA-27300 ORA-27301 ORA-27302
$ srvctl start database -db db01 PRCR-1079 : Failed to start resource ora.db01.db CRS-5017: The resource action "ora.db01.db start" encountered the following error: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/db01/parameterfile/spfiledb01.ora' ORA-17503: ksfdopn:10 Failed to open file +DATA/db01/parameterfile/spfiledb01.ora ORA-27300: OS system dependent operation:open failed with status: 13 ORA-27301: OS failure message: Permission denied ORA-27302: failure occurred at: sskgmsmr_7 . For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/host01/crs/trace/ohasd_oraagent_grid.trc". CRS-2674: Start of 'ora.db01.db' on 'host01' failed
ORA-27300: OS system dependent operation:%s failed with status: %s ORA-27301: OS failure message: %s ORA-27302: failure occurred at: %s
ORA-27300: OS system dependent operation:open failed with status: 13 ORA-27301: OS failure message: Permission denied ORA-27302: failure occurred at: sskgmsmr_7
$ ls -ld /u01/app/grid/product/19.0.0/grid_1/dbs drwxr-xr-x 2 grid oinstall 256 Feb 09 14:39 /u01/app/grid/product/19.0.0/grid_1/dbs $ cd /u01/app/grid/product/19.0.0/grid_1/dbs $ ls -l total 56 -rw-rw---- 1 grid oinstall 2304 Feb 09 14:39 ab_+ASM.dat -rw-rw---- 1 grid oinstall 1544 Feb 09 15:00 hc_+ASM.dat -rw-r----- 1 grid oinstall 57 Sep 25 2020 init+ASM.ora -rw-r--r-- 1 grid oinstall 3079 May 16 2015 init.ora -rw-r----- 1 grid oinstall 9728 Sep 25 2020 orapw+ASM
$ chmod o+r ab_+ASM.dat
$ ls -l
total 56
-rw-rw-r-- 1 grid oinstall 2304 Feb 09 14:39 ab_+ASM.dat
-rw-rw---- 1 grid oinstall 1544 Feb 09 15:09 hc_+ASM.dat
-rw-r----- 1 grid oinstall 57 Sep 25 2020 init+ASM.ora
-rw-r--r-- 1 grid oinstall 3079 May 16 2015 init.ora
-rw-r----- 1 grid oinstall 9728 Sep 25 2020 orapw+ASM
Thursday, March 3, 2022
Oracle SQLcl installation and configuration
Saturday, February 26, 2022
Script Find the Regular Source File of the Multi-level Symbolic Link on Linux / Unix
-rw-r--r-- 1 user01 group01 12 Feb 26 14:08 /dir1/file1.txt lrwxrwxrwx 1 user01 group01 17 Feb 26 14:13 /dir2/file2.lnk -> ../dir1/file1.txt lrwxrwxrwx 1 user01 group01 30 Feb 26 14:14 /dir3/file3.lnk -> /dir2/file2.lnk lrwxrwxrwx 2 user01 group01 17 Feb 26 14:18 /dir4/file4_1.lnk -> ../dir3/file3.lnk lrwxrwxrwx 1 user01 group01 11 Feb 26 14:21 /dir4/file4_2.lnk -> file4_1.lnk
$ findSrc.sh /dir4/file4_2.lnk /dir1/file1.txt
#!/bin/bash srcFile="$1" while [ -h "$srcFile" ] do tmpStr=`ls -ld "$srcFile"` tmpSrc=`expr "$tmpStr" : '.*-> \(.*\)$'` if expr "$tmpSrc" : '/.*' > /dev/null; then srcFile="$tmpSrc" else srcFile=`dirname "$srcFile"`"/$tmpSrc" fi done if [[ $srcFile == *".."* ]] || [[ $srcFile == *"."* ]] ; then workDir=`pwd` tmpDir=`dirname "$srcFile"` tmpFile=`basename "$srcFile"` tmpDir=`cd "$tmpDir" > /dev/null && pwd` if [ x"$tmpDir" != x ]; then srcFile="$tmpDir""/$tmpFile" fi cd "$workDir" fi echo $srcFile
$ ls -ld /dir4/file4 lrwxrwxrwx 1 oracle oinstall 1 Feb 26 08:16 /dir4/file4 -> file3
Monday, February 21, 2022
Oracle RAC 19c pdb open failed with ORA-12963: A read-only instance cannot be the first instance to open a database
sys@rac01> alter pluggable database pdb1 open; alter pluggable database pdb1 open * ERROR at line 1: ORA-12963: A read-only instance cannot be the first instance to open a database.
sys@rac02> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 pdb1 MOUNTED sys@rac02> alter pluggable database pdb1 open; Pluggable database altered. sys@rac02> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 pdb1 READ WRITE NO
sys@rac01> alter pluggable database pdb1 open; Pluggable database altered. sys@rac01> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 pdb1 READ ONLY NO
[oracle@rac01]$ srvctl status database -db orcl -verbose Instance orcl_1 is running on node rac02. Instance status: Open. Instance orcl_2 is running on node rac01. Instance status: Open,Readonly.
[oracle@rac01]$ srvctl config database -db orcl Database unique name: orcl Database name: orcl Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1 Oracle user: oracle Spfile: +DATA/ORCL/PARAMETERFILE/spfile.299.1096020569 Password file: +DATA/ORCL/PASSWORD/pwdorcl.289.1096019487 Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: dbpool,pool2 PQ Helper Server Pools: pool2 Disk Groups: DATA,FRA Mount point paths: Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: dba OSOPER group: oper Database instances: Configured nodes: CSS critical: no CPU count: 0 Memory target: 0 Maximum memory: 0 Default network number for database services: Database is policy managed
[oracle@rac01]$ srvctl status srvpool Server pool name: Free Active servers count: 0 Server pool name: Generic Active servers count: 0 Server pool name: dbpool Active servers count: 1 Server pool name: pool2 Active servers count: 1
[oracle@rac01]$ srvctl stop database -db orcl
[oracle@rac01]$ srvctl modify database -db orcl -pqpool
[oracle@rac01]$ srvctl config database -db orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.299.1096020569
Password file: +DATA/ORCL/PASSWORD/pwdorcl.289.1096019487
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: dbpool
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances:
Configured nodes:
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is policy managed
[oracle@rac01]$ srvctl remove srvpool -serverpool pool2 [oracle@rac01]$ [oracle@rac01]$ srvctl status srvpool Server pool name: Free Active servers count: 0 Server pool name: Generic Active servers count: 0 Server pool name: dbpool Active servers count: 2
Wednesday, February 16, 2022
Oracle 12.2 EXPDP Fails Exporting Full PDB with ORA-39126 On KUPW$WORKER.FETCH_XML_OBJECTS
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.KUPW$WORKER", line 12630 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 9571 ORA-06512: at "SYS.DBMS_METADATA", line 2976 ORA-06512: at "SYS.DBMS_METADATA", line 3608 ORA-06512: at "SYS.DBMS_METADATA", line 5008 ORA-06512: at "SYS.DBMS_METADATA", line 5327 ORA-06512: at "SYS.DBMS_METADATA", line 9552 ORA-06512: at "SYS.KUPW$WORKER", line 15119 ----- PL/SQL Call Stack ----- object line object handle number name 900000045fb46b00 33543 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION 900000045fb46b00 12651 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR 900000045fb46b00 15452 package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS 900000045fb46b00 3917 package body SYS.KUPW$WORKER.UNLOAD_METADATA 900000045fb46b00 13746 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS 900000045fb46b00 2439 package body SYS.KUPW$WORKER.MAIN b00000055ff65380 2 anonymous block DBMS_METADATA.SET_FILTER DBMS_METADATA.SET_FILTER DBMS_METADATA.SET_FILTER KUPW: In FETCH_XML_OBJECTS KUPW: End seqno is: 24 KUPF$FILE.OPEN_CONTEXT KUPF$FILE.OPEN_CONTEXT DBMS_METADATA.FETCH_XML_CLOB DBMS_METADATA.FETCH_XML_CLOB KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-00942: table or view does not exist
sys@orcl> alter session set container=cdb$root; Session altered. sys@orcl> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 pdb01 READ WRITE NO sys@orcl> select comp_id,comp_name from dba_registry; COMP_ID COMP_NAME ------------------------------ -------------------------------------------------- CATALOG Oracle Database Catalog Views CATPROC Oracle Database Packages and Types JAVAVM JServer JAVA Virtual Machine XML Oracle XDK CATJAVA Oracle Database Java Packages APS OLAP Analytic Workspace RAC Oracle Real Application Clusters XDB Oracle XML Database OWM Oracle Workspace Manager CONTEXT Oracle Text ORDIM Oracle Multimedia SDO Spatial XOQ Oracle OLAP API OLS Oracle Label Security APEX Oracle Application Express DV Oracle Database Vault sys@orcl> alter session set container=PDB$SEED; Session altered. sys@orcl> select comp_id,comp_name from dba_registry; COMP_ID COMP_NAME ------------------------------ -------------------------------------------------- CATALOG Oracle Database Catalog Views CATPROC Oracle Database Packages and Types JAVAVM JServer JAVA Virtual Machine XML Oracle XDK CATJAVA Oracle Database Java Packages APS OLAP Analytic Workspace RAC Oracle Real Application Clusters XDB Oracle XML Database OWM Oracle Workspace Manager CONTEXT Oracle Text ORDIM Oracle Multimedia SDO Spatial XOQ Oracle OLAP API OLS Oracle Label Security APEX Oracle Application Express DV Oracle Database Vault
sys@orcl> alter session set container=pdb01;
Session altered.
sys@orcl> select comp_id,comp_name from dba_registry;
COMP_ID COMP_NAME
------------------------------ --------------------------------------------------
CATALOG Oracle Database Catalog Views
CATPROC Oracle Database Packages and Types
RAC Oracle Real Application Clusters
OWM Oracle Workspace Manager
XDB Oracle XML Database
alter pluggable database all open;
@?/rdbms/admin/dpload.sql
@?/rdbms/admin/utlrp.sql
Wednesday, February 9, 2022
Configure Reverse Path Filter parameter "rp_filter" on Oracle Linux and Red Hat Enterprise Linux
When installing Oracle Grid Infrastructure (GI) 12c or higher version (18c, 19c, 21c), multiple network interfaces (NIC) can be used as private interconnection to implement inter-communication load balance and failover. However, if this configuration is adopted on Oracle Linux (OL) 6 or Red Hat Enterprise Linux (RHEL) 6 or higher, the GI installation prerequists checking shows error
Saturday, February 5, 2022
Oracle Cloud Infrastructure Plugin xxx not present for instance ocid1.instance.oc1.*
Wednesday, February 2, 2022
Oracle 19c GI installation failed with PRVG-13605 : Query of chrony daemon for its time sources could not be performed
The issue happens on Oracle Linux / Red Hat Enterprise Linux 7/8 on which chronyd is adopted as the network time protocol daemon instead of ntpd. When installing Oracle Grid Infrastructure 19c on these platform, the installation fails during prerequisit check, the message shows that
Verifying Network Time Protocol (NTP) ...FAILED Verifying chrony daemon is synchronized with at least one external time source ...FAILED rac01: PRVG-13605 : Query of chrony daemon for its time sources could not be performed on node "rac01". PRVG-2043 : Command "/usr/bin/chronyc sources " failed on node "rac01" and produced the following output: 506 Cannot talk to daemon rac02: PRVG-13605 : Query of chrony daemon for its time sources could not be performed on node "rac02". PRVG-2043 : Command "/usr/bin/chronyc sources " failed on node "rac02" and produced the following output: 506 Cannot talk to daemon
[root@rac01]# systemctl status chronyd ● chronyd.service - NTP client/server Loaded: loaded (/usr/lib/systemd/system/chronyd.service; enabled; vendor preset: enabled) Active: active (running) since Wed 2022-02-02 17:10:33 EST; 22s ago Docs: man:chronyd(8) man:chrony.conf(5) Process: 43769 ExecStartPost=/usr/libexec/chrony-helper update-daemon (code=exited, status=0/SUCCESS) Process: 43764 ExecStart=/usr/sbin/chronyd $OPTIONS (code=exited, status=0/SUCCESS) Main PID: 43768 (chronyd) Tasks: 1 (limit: 3355442) Memory: 376.0K CGroup: /system.slice/chronyd.service └─43768 /usr/sbin/chronyd [root@rac01 ~]# chronyc sources 210 Number of sources = 4 MS Name/IP address Stratum Poll Reach LastRx Last sample =============================================================================== ^? ts1.lab.dbaplus.ca 2 6 3 18 -166us[ -166us] +/- 17ms ^? ts2.lab.dbaplus.ca 2 6 3 17 +427us[ +427us] +/- 25ms [root@rac01 ~]# su - grid Last login: Wed Feb 2 20:11:48 EST 2022 on pts/1 [grid@rac01]$ chronyc sources 506 Cannot talk to daemon
cmdport 0This line can be commented out (add # at the beginging of the line) to enable chronyc management connctions. The line as following
# cmdport 0
systemctl restart chronyd
Friday, December 31, 2021
EM 12c/13c Send Email Notification When OMS or Repository Database Down
emd_email_gateway=<email_SMTP_server> emd_email_address=<email_address_of_notification_receivers> emd_from_email_address=<email_address_of_notification_sender>Line 2 set email address(es) to which the email notification will be sent. You can specify more than one email address separated by commas. For example, in my lab system, it is configures as following
emd_email_gateway=mail.lab.dbaplus.ca emd_email_address=dba@lab.dbaplus.ca,sa@lab.dbaplus.ca emd_from_email_address=oemadmin@lab.dbaplus.ca3. Start agent
EM 13c Agent is unreachable as its first severity has not yet come after blackout end
The agent status shows "Agent Unreachable (Post Blackout)" in Oracle Enterprise Manager 13c console. On the agent home page, it shows following error message,
Agent is unreachable as its first severity has not yet come after blackout end.
It is seen in EM 13c, can can be fixed by running following commands on agent host,
Thursday, December 30, 2021
RMAN left defunct server processes and NetBackup nborautil processes
$ ps -ef | grep nborautil oracle 17236328 6489154 31 12:14:13 - 129:18 -bprdtype 2 -use_stdin -client host01 -bprd -noxmloutput -ignorenamespace -jsonoutput 26 -eoichar /usr/openv/netbackup/bin/nborautil oracle 32506230 5440300 26 12:47:03 - 113:20 -bprdtype 2 -use_stdin -client host01 -bprd -noxmloutput -ignorenamespace -jsonoutput 26 -eoichar /usr/openv/netbackup/bin/nborautil $ ps -ef | grep 6489154 oracle 17236328 6489154 30 12:14:13 - 129:44 -bprdtype 2 -use_stdin -client host01 -bprd -noxmloutput -ignorenamespace -jsonoutput 26 -eoichar /usr/openv/netbackup/bin/nborautil oracle 6489154 1 0 Dec 27 - 11:47 oracleORCL (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) $ $ ps -ef | grep 5440300 oracle 32506230 5440300 24 12:47:03 - 113:44 -bprdtype 2 -use_stdin -client host01 -bprd -noxmloutput -ignorenamespace -jsonoutput 26 -eoichar /usr/openv/netbackup/bin/nborautil oracle 5440300 1 0 Dec 28 - 10:19 oracleORCL (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
sys@ORCL> select s.sid,s.serial#,s.username,s.machine,s.program,s.event 2 from v$session s, v$process p 3 where s.paddr=p.addr and p.spid in (5440300,6489154); SID SERIAL# USERNAME MACHINE PROGRAM EVENT ----- ---------- --------- -------- ------------------------- ------------------ 300 44503 SYS host01 rman@host01 (TNS V1-V3) Backup: MML shutdown 178 17334 SYS host01 rman@host01 (TNS V1-V3) Backup: MML shutdown
Friday, December 10, 2021
Mount DVD/CDROM with command on Linux CentOS / Red Hat / Oracle Linux / Ubuntu
[root@RHEL4]# cat /proc/sys/dev/cdrom/info CD-ROM information, Id: cdrom.c 3.20 2003/12/17 drive name: sr1 sr0 drive speed: 1 32 drive # of slots: 1 1 Can close tray: 1 1 Can open tray: 1 1 Can lock tray: 1 1 Can change speed: 0 1 Can select disk: 0 0 Can read multisession: 1 1 Can read MCN: 1 1 Reports media changed: 1 1 Can play audio: 1 1 Can write CD-R: 0 0 Can write CD-RW: 0 0 Can read DVD: 0 1 Can write DVD-R: 0 0 Can write DVD-RAM: 0 0 Can read MRW: 0 1 Can write MRW: 0 1 Can write RAM: 0 1
[root@RHEL4]# mkdir -p /media/cdrom
[root@RHEL4]# mount /dev/sr0 /media/cdrom mount: block device /dev/sr0 is write-protected, mounting read-only
Sunday, December 5, 2021
Oracle 19c Installation on Red Had Enterprise Linux 8 / Oracle Linux 8 – [INS-08101] Unexpected error while executing the action at state: ‘supportedOSCheck’
[INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'
Cause - No additional information available. Action - Cantact Oracle Spport Services or refer to the software manual. Summary - java.lang.NullPointerException
./gridSetup.sh -applyRU <GI_RU_UNZIP_DIR> - Install Grid Infrastructure ./runInstaller -applyRU <GI_RU_UNZIP_DIR> - Insall Database
export CV_ASSUME_DISTID=OEL8
Wednesday, December 1, 2021
Use oradebug To List All Internal Events Set In Oracle Database
# -- Set sql_trace (event 10046) to level 12 user01@orcl> alter session set events '10046 trace name context forever, level 12'; # -- Unset/disable sql_trace (event 10046) user01@orcl> alter session set events '10046 trace name context off'; # -- Set event for ORA-1652: unable to extend temp segment by %s in tablespace %s user01@orcl> alter session set events '1652 trace name errorstack'; # -- Unset event for ORA-1652 user01@orcl> alter session set events '1652 trace name context off';
# -- Set sql_trace (event 10046) to level 12 user01@orcl> alter system set events '10046 trace name context forever, level 12'; # -- Unset/disable sql_trace (event 10046) user01@orcl> alter system set events '10046 trace name context off'; # -- Set event for ORA-1652: unable to extend temp segment by %s in tablespace %s user01@orcl> alter system set events '1652 trace name errorstack'; # -- Unset event for ORA-1652 user01@orcl> alter system set events '1652 trace name context off';
select p.pid, p.spid, p.pname, s.username, s.machine, s.program from v$process p, v$session s where p.addr = s.paddr;
PID SPID PNAME USERNAME MACHINE PROGRAM --- ------ ------ ---------- --------------- ---------------------- 45 24432 USER01 workstation01 sqlplus.exe
sys@orcl> oradebug setmypid Statement processed.
sys@sys@orcl> oradebug setorapid 45 Oracle pid: 45, Unix process pid: 24432, image: oracle@dbhost01
sys@sys@orcl> oradebug setospid 24432 Oracle pid: 45, Unix process pid: 24432, image: oracle@dbhost01
sys@orcl> oradebug eventdump system 1652 trace name errorstack
sys@orcl> oradebug eventdump process 1652 trace name errorstack sys@orcl> oradebug eventdump session sql_trace level=12 1652 trace name errorstack
Tuesday, November 30, 2021
Identify SQL statements consuming temporary tablespace
Monday, November 15, 2021
Oracle 19c root.sh not working while gridSetup.sh switchGridHome to software-only installation
Software-only version New version --------------------------- -------------------------- PATCH_HOME=false PATCH_HOME=true MOVE_HOME=false MOVE_HOME=true TRANSPARENT_MOVE_HOME=false TRANSPARENT_MOVE_HOME=true SWONLY_MULTINODE=true SWONLY_MULTINODE=false
Saturday, November 6, 2021
EM 12c/13c Agent Unreachable (Agent is running but is currently not read to accept client requests)
Host=host01.dbaplus.ca
Target type=Agent
Target name=host01.dbaplus.ca:3872
Categories=Availability
Message=Agent Unreachable (REASON = The agent is running but is currently not ready to accept client requests). Host is reachable.
Severity=Critical
Event reported time=Nov 6, 2021 12:36:48 PM EDT
Operating System=SunOS
Platform=sparc
Event Type=Target Availability
Event name=Status
Availability status=Agent Unreachable
Update Details:
Agent Unreachable (REASON = The agent is running but is currently not ready to accept client requests). Host is reachable.
${AGNET_INSTANCE_HOME}/bin/emctl getproperty agent -name MaxInComingConnections
$ emctl getproperty agent -name MaxInComingConnections Oracle Enterprise Manager Cloud Control 13c Release 5 Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved. MaxInComingConnections=50
$ emctl getproperty agent -name MaxInComingConnections
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
MaxInComingConnections is not a valid configuration property
MaxInComingConnections=200
Friday, November 5, 2021
Javascript/CSS: Add Copy-to-Clipboard Button in HTML Code Example
Wednesday, November 3, 2021
Javascript/CSS: Add Line Number to HTML Source Code Block
When I shared the source code in my blog before, I used to reference source code by highlighting the code lines with HTML tag. It was not an issue until I had many references to different lines of a long clode block. It could take too much of my time to highlight or mark the reference when I post an artical. Therefore, I added the line number to the source code in my posts. The line-numbered code block looks like,
Sunday, October 24, 2021
EM 12c/13c The listener is down: Failed to run lsnrctl
Friday, October 1, 2021
Change SYS password in Oracle Data Guard
Monday, August 30, 2021
Database startup failed with ORA-600 [dbgripmg_2: infinite init action] [ADR_CONTROL_AUX]
Wednesday, July 7, 2021
AQ Queue Monitor Does Not Change Message MSG_STATE from WAIT to READY on RAC
Friday, June 25, 2021
EM 12c/13c Update Oracle Home Path of Targets with SQL
Wednesday, June 23, 2021
EM 12c/13c Change Lifecycle Status with SQL
Tuesday, June 22, 2021
EM 12c/13c How to find out new discovered targets with SQL
Sunday, June 20, 2021
Script Run datapatch against all running instance in parallel
Friday, June 18, 2021
Install PostgreSQL on RHEL/OL/CentOS 6/7/8
Thursday, June 17, 2021
Java Stored Procedure failed with java.lang.OutOfMemoryError
Wednesday, June 16, 2021
Oracle 19.11 roothas.sh failed with "Out of memory" on AIX
Tuesday, June 15, 2021
Configure yum with proxy server on RHEL/OL/Fedora
Thursday, June 3, 2021
How to Permanently Change PowerShell Prompt
Sunday, May 16, 2021
Grid Infrastructure 12.2 restore OCR, Voting File and mgmtdb when disk group corrupted
Sunday, May 9, 2021
Windows How to log all output on the console to text file
Wednesday, May 5, 2021
OEM 12c/13c Database Target Discovered and Named with host name as suffix
Sunday, May 2, 2021
Oracle 19.11 deinstall failed with "ERROR: oracle/rat/tfa/util/ManageTfa"
Oracle 19c runInstaller failed with "undefined reference to 'jox_eujs_nowait_'"
Updated on Thursday, Oct 21, 2021 with following, The error is also found with RU 19.13 (19.13.0.0.211019). I believe it is same thing with 19.12, though I did not test it.
When installing Oracle 19c with patch apply of Database RU 19.11 and OJVM RU 19.11, runInstaller failed with message
Error in invoking target 'irman ioracle idrdactl idrdalsnr idrdaproc' of makefile '/u01/app/oracle/product/19.11.0/dbhome_1/rdbms/lib/ins_rdbms.mk'. See /u01/app/oraInventory/logs/InstallActions2021-05-01_08-40-24PM/installActions2021-05-01_08-40-24PM.log for details.