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 script

Run the script connected to SQLcl as the owner (parsing schema) of the application.
We need to repeat this script for each application.

DECLARE
    l_workspace_id   NUMBER;
BEGIN
    SELECT workspace_id
      INTO l_workspace_id
      FROM apex_workspaces
     WHERE workspace = 'OPTRUST';

    --
    apex_application_install.set_workspace_id (l_workspace_id);
    apex_application_install.set_application_id (&1);
    apex_application_install.generate_offset;
    apex_application_install.set_schema ('OPTRUST');
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;

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;

Duplicate ACL for one schema. In this example, we use APEX_200200. Copy the creation script output and run it on the destination database.

SELECT    'BEGIN DBMS_NETWORK_ACL_ADMIN.append_host_ace (
            HOST         => '''
       || dna.HOST
       || ''',
            lower_port   => '
       || dna.lower_port
       || ',
            upper_port   => '
       || dna.upper_port
       || ',
            ace          =>
                xs$ace_type (privilege_list   => xs$name_list (''connect''),
                             principal_name   => '''
       || dnap.principal
       || ''',
                             principal_type   => xs_acl.ptype_db)); END;
                             /' v_sql
  FROM dba_network_acls dna, dba_network_acl_privileges dnap
 WHERE dna.aclid = dnap.aclid AND dnap.principal = 'APEX_200200';

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