sql user/password@//hostname/service_name
sql user/password@jdbc:oracle:oci:@(description=(address_list=(address=.......ODS)))
sql user/password@(description=(address_list=(address=.......ODS)))
Wednesday, August 26, 2020
SQLcl connect to Oracle
EZconnect syntax
Export AD Users in json format
Get-ADUser -Filter {(ObjectClass -eq "user") -and (Enabled -eq $true)} -property name,objectGUID,accountExpires,c,co,company,department,displayName,givenName,homePhone,l,mail,mailNickname,manager,physicalDeliveryOfficeName,postalCode,sAMAccountName,sn,st,streetAddress,telephoneNumber,title,userPrincipalName,facsimileTelephoneNumber,whenChanged,whenCreated,mobile,distinguishedName -SearchBase "DC=leavemealone,DC=local" | Select-object name,objectGUID,accountExpires,c,co,company,department,displayName,givenName,homePhone,l,mail,mailNickname,manager,physicalDeliveryOfficeName,postalCode,sAMAccountName,sn,st,streetAddress,telephoneNumber,title,userPrincipalName,facsimileTelephoneNumber,whenChanged,whenCreated,mobile,distinguishedName | ConvertTo-Json | Out-File $ad_user_json -encoding UTF8
Tuesday, August 18, 2020
FreeBSD Ports Management
Refresh Ports Collection
# rm -rf /usr/ports/.* /usr/ports/*
# svnlite co svn://svn.freebsd.org/ports/tags/RELEASE_11_3_0 /usr/ports
List all outdated Ports
# pkg version -l "<"
Update one Port
# portmaster -G devel/pkgconf
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
Job notification setup
Check the job notification 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.
tnsnames.ora
SQL*Plus with TAF
SQLcl with TAF
SQL Developer with TAF
Change connection type to Advanced and then put this in Custom JDBC URL
Add and review TAF service
tnsnames.ora
SQL*Plus with TAF
SQLcl with TAF
SQL Developer with TAF
Change connection type to Advanced and then put this in Custom JDBC URL
To verify the connection is actually using 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;
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.
New way of adding ACL in Oracle 12c
Check ACL and privileges under SYS
Add missing ACL after upgrading APEX
Duplicate ACL for one schema. In this example, we use APEX_200200. Copy the creation script output and run it on the destination database.
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';
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.
Run this in sqlplus to resolve the issue.
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
Subscribe to:
Posts (Atom)