Wednesday, May 27, 2020

Oracle RAC status check

crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.DATA.dg
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.net1.network
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.ons
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE
ora.proxy_advm
               OFFLINE OFFLINE      rac1                     STABLE
               OFFLINE OFFLINE      rac2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac1                     STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       rac1                     192.168.50.10,STABLE
ora.asm
      1        ONLINE  ONLINE       rac1                     Started,STABLE
      2        ONLINE  ONLINE       rac2                     Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       rac1                     STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       rac1                     Open,STABLE
ora.orcl.db
      1        ONLINE  ONLINE       rac1                     Open,HOME=C:\oracle\
                                                             product\12.2.0\dbhom
                                                             e_1,STABLE
      2        ONLINE  ONLINE       rac2                     Open,HOME=C:\oracle\
                                                             product\12.2.0\dbhom
                                                             e_1,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       rac2                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       rac1                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       rac1                     STABLE
--------------------------------------------------------------------------------

srvctl config database -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: C:\oracle\product\12.2.0\dbhome_1
Oracle user: nt authority\system
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.298.976826851
Password file: +DATA/ORCL/PASSWORD/pwdorcl.282.976826257
Domain: leavemealone.local
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
Database instances: orcl1,orcl2
Configured nodes: rac1,rac2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

srvctl config scan
SCAN name: RAC-scan, Network: 1
Subnet IPv4: 10.10.2.0/255.255.255.0/NIC1, static
Subnet IPv6:
SCAN 1 IPv4 VIP: 10.10.2.111
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 2 IPv4 VIP: 10.10.2.110
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 3 IPv4 VIP: 10.10.2.109
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:

srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac2
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac1
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac1

cluvfy comp ocr -n all -verbose

Verifying OCR Integrity ...PASSED

Verification of OCR integrity was successful.

CVU operation performed:      OCR integrity
Date:                         May 30, 2018 11:54:26 PM
CVU home:                     C:\oracle_grid_home\bin\..\
User:                         leavemealone\scammisuli

ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       2124
         Available space (kbytes) :     407444
         ID                       : 2115914722
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

Oracle Scheduler Job Notification

Initial system setup
BEGIN
DBMS_SCHEDULER.create_credential (credential_name   => 'MAILSERVER_CREDENTIAL',
username          => 'uuuuuuuu',
password          => 'xxxxxxxx');
END;

BEGIN
DBMS_SCHEDULER.set_scheduler_attribute ('email_server', 'vtordc01.leavemealone.local:25');
DBMS_SCHEDULER.set_scheduler_attribute ('email_sender', 'service@leavemealone.ca');
DBMS_SCHEDULER.set_scheduler_attribute ('email_server_credential', 'MAILSERVER_CREDENTIAL');
END;

Job notification setup
BEGIN
DBMS_SCHEDULER.add_job_email_notification (job_name     => 'ADHOC_04',
recipients   => 'prtg.alert@gmail.com',
events       => 'JOB_STARTED,JOB_SUCCEEDED,JOB_FAILED,JOB_STOPPED');
END;

Check the job notification setup
SELECT job_name,
recipient,
event,
filter_condition
FROM user_scheduler_notifications;

Oracle Transparent Application Failover (TAF)

TAF is based on OCI (Oracle Call Interface). So every client using OCI can/may profit from TAF.
JDBC Thick requires an Oracle Client to be installed, hence you have OCI and therefore TAF.
JDBC Thin does not require an Oracle Client to be installed (just the appropriate JAR files) and is not based on OCI, hence NO TAF.

You need to install the Oracle instant client first.

Below is a client-side TAF setup


tnsnames.ora
RACPROD =
  (DESCRIPTION=
    (FAILOVER = ON)
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=rac-scan.leavemealone.local)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=prod.leavemealone.local)
      (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))
    )
  )

SQL*Plus with TAF
sqlplus accdb@racprod

SQLcl with TAF
sqlcl accdb@jdbc:oracle:oci:@(DESCRIPTION=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prod.leavemealone.local)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))))

SQL Developer with TAF
Change connection type to Advanced and then put this in Custom JDBC URL
jdbc:oracle:oci:@(DESCRIPTION=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prod.leavemealone.local)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))))

Below is a server-side TAF setup


Add and review TAF service
srvctl add service -d orcl -s prodtaf.leavemealone.local -m BASIC -e SELECT -z 180 -w 5 -j LONG -r "ORCL1,ORCL2"
-pdb prod
srvctl start service -d orcl -s prodtaf.leavemealone.local
srvctl config service -d orcl -s prodtaf.leavemealone.local
Service name: prodtaf.leavemealone.local
Server pool:
Cardinality: 2
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 180
TAF failover delay: 5
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: prod
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: orcl1,orcl2
Available instances:
CSS critical: no

tnsnames.ora
RACPROD =
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=rac-scan.leavemealone.local)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=prodtaf.leavemealone.local)
    )
  )

SQL*Plus with TAF
sqlplus accdb@racprod

SQLcl with TAF
sqlcl accdb@jdbc:oracle:oci:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prodtaf.leavemealone.local)))

SQL Developer with TAF
Change connection type to Advanced and then put this in Custom JDBC URL
jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=rac-scan.leavemealone.local)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=prodtaf.leavemealone.local)))

To verify the connection is actually using TAF
SELECT inst_id,
         machine,
         program,
         username,
         logon_time,
         failover_type,
         failover_method,
         failed_over
    FROM gv$session
ORDER BY logon_time DESC;

APEX Office Print plug-in upgrade

Run the script connected to SQLcl as the owner (parsing schema) of the application
declare
l_workspace_id number;
begin
select workspace_id into l_workspace_id
from apex_workspaces
where workspace = 'WORKSPACENAME';
--
apex_application_install.set_workspace_id( l_workspace_id );
apex_application_install.set_application_id( 161 );
apex_application_install.generate_offset;
apex_application_install.set_schema( 'SCHEMANAME' );
end;
/
@dynamic_action_plugin_be_apexrnd_aop_convert_da.sql
@dynamic_action_plugin_be_apexrnd_aop_da.sql
@process_type_plugin_be_apexrnd_aop.sql
commit;

Thursday, May 21, 2020

Fine-Grained Access to External Network Services

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.

DECLARE
acl_path     VARCHAR2 (4000);
v_hostname   VARCHAR2 (200)  := 'xxx.leavemealone.local';
v_user       VARCHAR2 (30)   := 'APEX_050100';
BEGIN
SELECT acl
INTO acl_path
FROM dba_network_acls
WHERE HOST = v_hostname AND lower_port IS NULL AND upper_port IS NULL;

IF dbms_network_acl_admin.check_privilege (acl_path, v_user, 'connect') IS NULL
THEN
dbms_network_acl_admin.add_privilege (acl_path, v_user, TRUE, 'connect');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
dbms_network_acl_admin.create_acl ('send_mail.xml', 'send_mail ACL', v_user, TRUE, 'connect');
dbms_network_acl_admin.assign_acl ('send_mail.xml', v_hostname, 25, 25);
END;
/
COMMIT;


New way of adding ACL in Oracle 12c
BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'xxx.leavemealone.local', 
    lower_port => 25,
    upper_port => 25,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'APEX_050100',
                              principal_type => xs_acl.ptype_db)); 
END;
/
COMMIT;


Check ACL and privileges under SYS
SELECT * FROM dba_network_acls;

SELECT * FROM dba_network_acl_privileges order by principal;

Add missing ACL after upgrading APEX
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege ('/sys/acls/power_users.xml',
'APEX_050100',
TRUE,
'connect');
DBMS_NETWORK_ACL_ADMIN.add_privilege ('/sys/acls/send_mail.xml',
'APEX_050100',
TRUE,
'connect');
END;

Saturday, May 9, 2020

Export APEX application/workspace with SQLcl

Export one application

apex export -applicationid 102 -split -skipExportDate -expTranslations -expPubReports -expSupportingObjects N -dir c:\backup

Export all applications

apex export -instance -applicationid 100 -split -skipExportDate -expTranslations -expPubReports -expSupportingObjects N -dir c:\backup

Export all workspaces

apex export -expWorkspace -applicationid 100 -expFiles -skipExportDate -dir c:\backup

Wednesday, February 26, 2020

How to Resolve “ORA-29548: Java system class reported”

After applying Jan 14th 2020 CPU patch, one of the java program in the database getting error.
ORA-29548: Java system class reported: joxcsys: release mismatch, 
12.2.0.1.180717 1.8 in database (classes.bin) vs 12.2.0.1.0 1.8 in executable

Run this in sqlplus to resolve the issue.
@?/javavm/install/update_javavm_db.sql

Thursday, November 7, 2019

Tuning Tomcat for APEX/ORDS in Production

Logging

Configuration File: $CATALINA_HOME/conf/logging.properties
Change all occurrences of FINE and INFO to SEVERE

JVM Options

Run tomcat9w.exe
Change Initial Memory pool and Maximum memory pool to 4096.


Main Tomcat Server Configurations

Configuration File: $CATALINA_HOME/conf/server.xml
<Connector port="443" protocol="org.apache.coyote.http11.Http11AprProtocol" 
  SSLEnabled="true" scheme="https" secure="true"
  maxHttpHeaderSize="32767"
  URIEncoding="UTF-8"
  compression="on"
  acceptorThreadCount="2"
  acceptCount="10"
  maxConnections="200"
  maxThreads="200"
  minSpareThreads="10"
  connectionTimeout="30000"
  disableUploadTimeout="false"
  connectionUploadTimeout="300000">

...

</Connector>
Close to the end of server.xml
<Host name="localhost" appBase="webapps"
            unpackWARs="true" autoDeploy="true">

        <Context path="/ords" reloadable="false" />

...

</Host>

Oracle REST Data Service

Configuration File: defaults.xml
Change the limits
<entry key="jdbc.InitialLimit">20</entry>
<entry key="jdbc.MinLimit">20</entry>
<entry key="jdbc.MaxLimit">50</entry>
<entry key="jdbc.MaxStatementsLimit">20</entry>

Wednesday, September 11, 2019

UNDO tablespace growing non-stop Part 2

This the original post and afterward we find out the culprit is the Flashback Data Archive (FDA).

It is a simple fix. We just need to bounce the FDA and within a few hours, oracle should release all the spaces.

BEGIN
  dbms_flashback_archive.disable_application(application_name => 'PARIS_AUDIT');
  dbms_flashback_archive.enable_application(application_name => 'PARIS_AUDIT');
END;
/

Thursday, July 11, 2019

Oracle sql statement audit for an user

Enable Audit for an user
CREATE AUDIT POLICY penweb_api audit_policy
ACTIONS DELETE, INSERT, UPDATE, SELECT
WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''PENWEB_API'''
EVALUATE PER SESSION;

AUDIT POLICY penweb_api_audit_policy;

SELECT object_schema,
object_name,
object_type,
audit_option,
condition_eval_opt,
audit_condition
FROM   audit_unified_policies
WHERE  policy_name = 'PENWEB_API_AUDIT_POLICY';

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

Check the Audit trail
EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

SELECT *
FROM   unified_audit_trail
WHERE  dbusername = 'PENWEB_API'
ORDER BY event_timestamp desc;

Remove Audit
NOAUDIT POLICY penweb_api_audit_policy;
DROP POLICY penweb_api_audit_policy;

Monday, June 17, 2019

Remote Desktop Services Manager Command Line

From time to time, we cannot RDP into servers because the maximum # of users limit has been reached.
Here is what we do to kill the orphan sessions to make room.

List of Sessions

C:\Users\CyberArkDBA>qwinsta /server:parisdb02p
SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE
services                                    0  Disc
console                                     1  Conn
CyberArkDBA                                 9  Disc
31c5ce94259d4...                        65536  Listen
rdp-tcp                                 65537  Listen

Reset a Session

Look for the ID that you want to reset. In this case, it is ID #9.
C:\Users\CyberArkDBA>rwinsta 9 /server:parisdb02p /V

List of Sessions again

C:\Users\CyberArkDBA>qwinsta /server:parisdb02p
SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE
services                                    0  Disc
console                                     1  Conn
31c5ce94259d4...                        65536  Listen
rdp-tcp                                 65537  Listen