Feed aggregator

PL/SQL, AST, DIANA, Attributes and IDL

Pete Finnigan - 6 hours 26 min ago
I have been wanting to write a detailed post about this subject for a very long time and indeed I have had some notes and screen dumps for some of this for more than 15 years for some parts of....[Read More]

Posted by Pete On 06/04/20 At 08:57 PM

Categories: Security Blogs

Install Azure CLI on MacBook Pro

DBASolved - Mon, 2020-04-06 18:58

Microsoft has enabled its cloud platform to have a command line (CLI) utility that can interact with the cloud based services.  This CLI is installed on the Mac OS X operating system by using homebrew.   Homebrew To install homebrew, use the package manager.  Homebrew makes it easy to install the Azure CLI with very few commands. […]

The post Install Azure CLI on MacBook Pro appeared first on DBASolved.

Categories: DBA Blogs

What is the Zachman Framework ?

OracleApps Epicenter - Mon, 2020-04-06 10:55
In layman word Zachman Framework is matrix for managing enterprise architecture. The Zachman Framework uses a 36-column matrix to help organize your company’s enterprise architecture and lend insight into your organization’s IT assets. One axis, normally spread about the horizontal of the matrix, consists of the following fundamental elements of human analysis in template include […]
Categories: APPS Blogs

Using Materialized View as ETL option

Tom Kyte - Mon, 2020-04-06 06:46
Hello TOM, Any comment regarding Using Materialized View as ETL option to populate data from OLTP to OLAP? Pros and Cons will be useful. Thanks, Rajneesh
Categories: DBA Blogs

SAAS Vs IAAS Vs PAAS for DBCS related query

Tom Kyte - Mon, 2020-04-06 06:46
Hello TOM, I need to use DBCS for implementing Reporting Solution on cloud. Hence need to understand SAAS, IAAS and PAAS platform for cloud. Can you please help me to understand in simple layman terms with examples of all 3. Thanks, Rajnee...
Categories: DBA Blogs

Application Express Form

Tom Kyte - Mon, 2020-04-06 06:46
How can I make a text field bring the email of the person logged in?
Categories: DBA Blogs

Number of connections and sessions in a given time

Tom Kyte - Mon, 2020-04-06 06:46
Hello, Ask Tom Team. I would like to know the number of sessions and connections to an Oracle database in a given time. Thanks in advanced.
Categories: DBA Blogs

Unable to connect to database xe using sql developer

Tom Kyte - Mon, 2020-04-06 06:46
Hi , I am unable to connect to oracle database using SQL DEVELOPER. It gives me" i/o error network adapter could not establish connection". Please let me know what are the possible reasons for it and provide me the solution for the same. - I am u...
Categories: DBA Blogs

Parsing out a field in MS Access that is CSV (JSON) data

Tom Kyte - Mon, 2020-04-06 06:46
I have a CSV data file that I load into MS Access. No problem doing that. I?ve got it all loaded. My issue is that one of the records in the file contains a field that contains CSV data (JSON data) which is line item invoice data (ProductData) a...
Categories: DBA Blogs

Common table expression using sample() referenced more than once does not produce correct results.

Tom Kyte - Mon, 2020-04-06 06:46
Hi Here is a very much simplified example of real world situation. <code> drop table lh_a; create table lh_a as (select level nro, cast(' ' as char(100)) dummy from dual connect by level < 1000000); create unique index lh_a_ind on l...
Categories: DBA Blogs

[AZ-900] Microsoft Azure Fundamentals: Cloud Computing – CapEx vs OpEx Model

Online Apps DBA - Mon, 2020-04-06 04:31

There are two ways of investing in Cloud resources: 1. Capital Expenditure(CapEx) 2. Operational Expenditure(OpEx) • Is Azure Reserved VM an example of the CapEx model? • What are the pros and cons of each of these models? Get answers to all these questions about Mircosoft Azure CapEx & OpEx pricing models on K21Academy’s blog […]

The post [AZ-900] Microsoft Azure Fundamentals: Cloud Computing – CapEx vs OpEx Model appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Thank you kubie exactly what I needed

Pas Apicella - Sun, 2020-04-05 22:59
On average I deal with at least 5 different Kubernetes clusters so today when I saw / heard of kubie I had to install it.

kubie is an alternative to kubectx, kubens and the k on prompt modification script. It offers context switching, namespace switching and prompt modification in a way that makes each shell independent from others

Installing kubie right now involved download the release from the link below. Homebrew support is pending

https://github.com/sbstp/kubie/releases

Once added to your path it's as simple as this

1. Check kubie is in your path

$ which kubie
/usr/local/bin/kubie

2. Run "kubie ctx" as follows and select the "apples" k8s context

papicella@papicella:~/pivotal/PCF/APJ/PEZ-HaaS/haas-236$ kubie ctx



[apples|default] papicella@papicella:~/pivotal/PCF/APJ/PEZ-HaaS/haas-236$

3. Switch to a new namespace as shown below and watch how the PS1 prompt changes to indicate the k8s conext and new namespace we have set as result of the command below

$ kubectl config set-context --current --namespace=vmware-system-tmc
Context "apples" modified.

[apples|vmware-system-tmc] papicella@papicella:~/pivotal/PCF/APJ/PEZ-HaaS/haas-236$

4. Finally kubie exec is a subcommand that allows you to run commands inside of a context, a bit like kubectl exec allows you to run a command inside a pod. Here is some examples below
  
[apples|vmware-system-tmc] papicella@papicella:~/pivotal/PCF/APJ/PEZ-HaaS/haas-236$ kubie exec apples vmware-system-tmc kubectl get pods
NAME READY STATUS RESTARTS AGE
agent-updater-75f88b44f6-9f9jj 1/1 Running 0 2d23h
agentupdater-workload-1586145240-kmwln 1/1 Running 0 3s
cluster-health-extension-76d9b549b5-dlhms 1/1 Running 0 2d23h
data-protection-59c88488bd-9wxk2 1/1 Running 0 2d23h
extension-manager-8d69d95fd-sgksw 1/1 Running 0 2d23h
extension-updater-77fdc4574d-fkcwb 1/1 Running 0 2d23h
inspection-extension-64857d4d95-nl76f 1/1 Running 0 2d23h
intent-agent-6794bb7995-jmcxg 1/1 Running 0 2d23h
policy-sync-extension-7c968c9dcd-x4jvl 1/1 Running 0 2d23h
policy-webhook-779c6f6c6-ppbn6 1/1 Running 0 2d23h
policy-webhook-779c6f6c6-r82h4 1/1 Running 1 2d23h
sync-agent-d67f95889-qbxtb 1/1 Running 6 2d23h
[apples|vmware-system-tmc] papicella@papicella:~/pivotal/PCF/APJ/PEZ-HaaS/haas-236$ kubie exec apples default kubectl get pods
NAME READY STATUS RESTARTS AGE
pbs-demo-image-build-1-mnh6v-build-pod 0/1 Completed 0 2d23h
[apples|vmware-system-tmc] papicella@papicella:~/pivotal/PCF/APJ/PEZ-HaaS/haas-236$

More Information

Blog Page:
https://blog.sbstp.ca/introducing-kubie/

GitHub Page:
https://github.com/sbstp/kubie
Categories: Fusion Middleware

Using sshUserSetup.sh for Passwordless ssh

Michael Dinh - Sun, 2020-04-05 07:03

Quick short and sweet. I am creating POC for Dataguard with multiple standby configuration using/hacking vagrant virtual box.

Being as lazy as I am and not liking to have to enter password, use sshUserSetup.sh

[oracle@ol7-121-dg1 ~]$ cd /u01/software/database/sshsetup/
[oracle@ol7-121-dg1 sshsetup]$


[oracle@ol7-121-dg1 sshsetup]$ ./sshUserSetup.sh -h
Please specify a valid and existing cluster configuration file.
Either user name or host information is missing
Usage ./sshUserSetup.sh -user <user name> [ -hosts "<space separated hostlist>" | -hostfile <absolute path of cluster configuration file> ] [ -advanced ]  [ -verify] [ -exverify ] [ -logfile <desired absolute path of logfile> ] [-confirm] [-shared] [-help] [-usePassphrase] [-noPromptPassphrase]
[oracle@ol7-121-dg1 sshsetup]$


[oracle@ol7-121-dg1 sshsetup]$ ./sshUserSetup.sh -user oracle -hosts "ol7-121-dg1 ol7-121-dg2 ol7-121-dg3" -noPromptPassphrase
The output of this script is also logged into /tmp/sshUserSetup_2020-04-05-11-53-56.log
Hosts are ol7-121-dg1 ol7-121-dg2 ol7-121-dg3
user is oracle
Platform:- Linux
Checking if the remote hosts are reachable
PING ol7-121-dg1.localdomain (192.168.56.101) 56(84) bytes of data.
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=1 ttl=64 time=0.016 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=2 ttl=64 time=0.019 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=3 ttl=64 time=0.036 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=4 ttl=64 time=0.045 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=5 ttl=64 time=0.041 ms

--- ol7-121-dg1.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4293ms
rtt min/avg/max/mdev = 0.016/0.031/0.045/0.012 ms
PING ol7-121-dg2.localdomain (192.168.56.102) 56(84) bytes of data.
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=1 ttl=64 time=0.333 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=2 ttl=64 time=0.657 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=3 ttl=64 time=0.547 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=4 ttl=64 time=0.539 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=5 ttl=64 time=0.514 ms

--- ol7-121-dg2.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4310ms
rtt min/avg/max/mdev = 0.333/0.518/0.657/0.104 ms
PING ol7-121-dg3.localdomain (192.168.56.103) 56(84) bytes of data.
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=1 ttl=64 time=0.356 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=2 ttl=64 time=0.554 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=3 ttl=64 time=0.463 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=4 ttl=64 time=0.362 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=5 ttl=64 time=0.472 ms

--- ol7-121-dg3.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4517ms
rtt min/avg/max/mdev = 0.356/0.441/0.554/0.076 ms
Remote host reachability check succeeded.
The following hosts are reachable: ol7-121-dg1 ol7-121-dg2 ol7-121-dg3.
The following hosts are not reachable: .
All hosts are reachable. Proceeding further...
firsthost ol7-121-dg1
numhosts 3
The script will setup SSH connectivity from the host ol7-121-dg1.localdomain to all
the remote hosts. After the script is executed, the user can use SSH to run
commands on the remote hosts or copy files between this host ol7-121-dg1.localdomain
and the remote hosts without being prompted for passwords or confirmations.

NOTE 1:
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. Since the script does not
store passwords, you may be prompted for the passwords during the execution of
the script whenever ssh or scp is invoked.

NOTE 2:
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE
directories.

Do you want to continue and let the script make the above mentioned changes (yes/no)?
yes

The user chose yes
User chose to skip passphrase related questions.
Creating .ssh directory on local host, if not present already
Creating authorized_keys file on local host
Changing permissions on authorized_keys to 644 on local host
Creating known_hosts file on local host
Changing permissions on known_hosts to 644 on local host
Creating config file on local host
If a config file exists already at /home/oracle/.ssh/config, it would be backed up to /home/oracle/.ssh/config.backup.
Creating .ssh directory and setting permissions on remote host ol7-121-dg1
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host ol7-121-dg1. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host ol7-121-dg1.
Warning: Permanently added 'ol7-121-dg1,192.168.56.101' (ECDSA) to the list of known hosts.
oracle@ol7-121-dg1's password:
Done with creating .ssh directory and setting permissions on remote host ol7-121-dg1.
Creating .ssh directory and setting permissions on remote host ol7-121-dg2
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host ol7-121-dg2. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host ol7-121-dg2.
Warning: Permanently added 'ol7-121-dg2,192.168.56.102' (ECDSA) to the list of known hosts.
oracle@ol7-121-dg2's password:
Done with creating .ssh directory and setting permissions on remote host ol7-121-dg2.
Creating .ssh directory and setting permissions on remote host ol7-121-dg3
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host ol7-121-dg3. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host ol7-121-dg3.
Warning: Permanently added 'ol7-121-dg3,192.168.56.103' (ECDSA) to the list of known hosts.
oracle@ol7-121-dg3's password:
Done with creating .ssh directory and setting permissions on remote host ol7-121-dg3.
Copying local host public key to the remote host ol7-121-dg1
The user may be prompted for a password or passphrase here since the script would be using SCP for host ol7-121-dg1.
oracle@ol7-121-dg1's password:
Done copying local host public key to the remote host ol7-121-dg1
Copying local host public key to the remote host ol7-121-dg2
The user may be prompted for a password or passphrase here since the script would be using SCP for host ol7-121-dg2.
oracle@ol7-121-dg2's password:
Done copying local host public key to the remote host ol7-121-dg2
Copying local host public key to the remote host ol7-121-dg3
The user may be prompted for a password or passphrase here since the script would be using SCP for host ol7-121-dg3.
oracle@ol7-121-dg3's password:
Done copying local host public key to the remote host ol7-121-dg3
cat: /home/oracle/.ssh/known_hosts.tmp: No such file or directory
cat: /home/oracle/.ssh/authorized_keys.tmp: No such file or directory
SSH setup is complete.

------------------------------------------------------------------------
Verifying SSH setup
===================
The script will now run the date command on the remote nodes using ssh
to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,
THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR
PASSWORDS. If you see any output other than date or are prompted for the
password, ssh is not setup correctly and you will need to resolve the
issue and set up ssh again.
The possible causes for failure could be:
1. The server settings in /etc/ssh/sshd_config file do not allow ssh
for user oracle.
2. The server may have disabled public key based authentication.
3. The client public key on the server may be outdated.
4. ~oracle or ~oracle/.ssh on the remote host may not be owned by oracle.
5. User may not have passed -shared option for shared remote users or
may be passing the -shared option for non-shared remote users.
6. If there is output in addition to the date, but no password is asked,
it may be a security alert shown as part of company policy. Append the
additional text to the <OMS HOME>/sysman/prov/resources/ignoreMessages.txt file.
------------------------------------------------------------------------
--ol7-121-dg1:--
Running /usr/bin/ssh -x -l oracle ol7-121-dg1 date to verify SSH connectivity has been setup from local host to ol7-121-dg1.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Apr  5 11:54:28 UTC 2020
------------------------------------------------------------------------
--ol7-121-dg2:--
Running /usr/bin/ssh -x -l oracle ol7-121-dg2 date to verify SSH connectivity has been setup from local host to ol7-121-dg2.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Apr  5 11:54:28 UTC 2020
------------------------------------------------------------------------
--ol7-121-dg3:--
Running /usr/bin/ssh -x -l oracle ol7-121-dg3 date to verify SSH connectivity has been setup from local host to ol7-121-dg3.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Apr  5 11:54:28 UTC 2020
------------------------------------------------------------------------
SSH verification complete.
[oracle@ol7-121-dg1 sshsetup]$

I wonder if Tim reads my blog?

__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5e8a477d8cadf', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

Why Name Listener?!

Michael Dinh - Sun, 2020-04-05 06:48

May be I am too naive to know, If you have reason, then please share.

With the following configuration, environment can be easily migrated/duplicated with minimal or no change.
The only change would be port for local_listener if port# changed.

Also, easier to use and maintain.

local_listener=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1591))
lsnrctl status
tnsnames.ora entry ***not*** required

With the following configuration, environment can be migrated/duplicated requiring multiple changes.

local_listener=LISTENER_NAME
lsnrctl status LISTENER_NAME
tnsnames.ora entry required for LISTENER_NAME

If listener is named, then does every environment has different names?

Security will mostly likely be used for justification but I don’t see it.

DATAGUARD sqlnet.ora NAMES.DEFAULT_DOMAIN

Michael Dinh - Sun, 2020-04-05 00:20

If you just want the solution, then read Database Startup Fails With ORA-00119 (Doc ID 471767.1)

From standby database, startup mount resulted in the following errors:

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'

When setting local_listener, my preference is:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))' scope=spfile sid='*';

However, some implementations will use the following:

alter system set local_listener=LISTENER scope=spfile sid='*';

There are pros and cons to both.

When using LISTENER with tnsnames, modifications can be performed from tnsnames.ora without having to modify database parameters.

However, it’s not forgiving when there are misconfiguration.

Demo 1:
Modify local_listener and restart DB.

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))' scope=spfile sid='*';

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------------------------
listener_networks                    string
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
remote_listener                      string
SQL>

Demo 2:
Modify local_listener and restart DB failed. Modify sqlnet.ora (delete NAMES.DEFAULT_DOMAIN=world).

### This is a bad omen and no changes should be made to DB until tnsping is resolved.
[oracle@ol7-121-dg2 ~]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-APR-2020 04:32:05

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name
[oracle@ol7-121-dg2 ~]$

### Restart DB FAILED
SQL> alter system set local_listener=LISTENER scope=spfile sid='*';

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'
SQL>

That’s not good!

Check sqlnet.ora

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=400
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)
NAMES.DEFAULT_DOMAIN=world

[oracle@ol7-121-dg2 ~]$ vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=400
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)
[oracle@ol7-121-dg2 ~]$

Did you see the problem? Not sure why NAMES.DEFAULT_DOMAIN=world was set and remove solved the issue.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      LISTENER
remote_listener                      string
SQL>

Demo 3:
Rollback sqlnet.ora (add NAMES.DEFAULT_DOMAIN=world), modify tnsnames.ora (LISTENER.world), and restart DB.

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=400
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)

NAMES.DEFAULT_DOMAIN=world

[oracle@ol7-121-dg2 ~]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-APR-2020 04:54:52

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name
[oracle@ol7-121-dg2 ~]$

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora
LISTENER.world = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))

hawka.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg1.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = hawk)
    )
  )

hawkb.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = hawk)
    )
  )

[oracle@ol7-121-dg2 ~]$ vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora

[oracle@ol7-121-dg2 ~]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-APR-2020 05:04:18

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))
OK (0 msec)

[oracle@ol7-121-dg2 ~]$

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      LISTENER
remote_listener                      string
SQL>

As demonstrated, having more options is not always good as it can lead to more likelihood for errors. Chose your evil wisely.

DATAGUARD Using DBCA Silent Mode Is Not Setting DB_UNIQUE_NAME

Michael Dinh - Sat, 2020-04-04 18:30

Unfortunately, db_name=db_unique_name which is not ideal for Data Guard environment.

Even without Data Guard, my preference is to have db_name different from db_unique_name.

Typically, I like to append letter to db_name vs using number which can be confused with RAC instance.

Another option is using airport code; however, this will be inaccurate when database is migrated to new DC.

If I can help it, then I would like implementation to be robust.

To resolve the issue, add -initparams db_unique_name=${NODE1_DB_UNIQUE_NAME} to dbca.

dbca -silent -createDatabase                                                 \
  -responseFile NO_VALUE                                                     \
  -templateName General_Purpose.dbc                                          \
  -sid ${ORACLE_SID}                                                         \
  -gdbname ${ORACLE_SID}                                                     \
  -characterSet AL32UTF8                                                     \
  -sysPassword ${SYS_PASSWORD}                                               \
  -systemPassword ${SYS_PASSWORD}                                            \
  -createAsContainerDatabase false                                           \
  -databaseType MULTIPURPOSE                                                 \
  -automaticMemoryManagement false                                           \
  -totalMemory 2048                                                          \
  -storageType FS                                                            \
  -datafileDestination "${DATA_DIR}"                                         \
  -redoLogFileSize 50                                                        \
  -emConfiguration NONE                                                      \
  -sampleSchema false                                                        \
  -initparams db_unique_name=${NODE1_DB_UNIQUE_NAME}                         \
  -ignorePreReqs

set lines 100
column NAME_COL_PLUS_SHOW_PARAM format a30
column VALUE_COL_PLUS_SHOW_PARAM format a55

SQL> set lines 100
SQL> column NAME_COL_PLUS_SHOW_PARAM format a30
SQL> column VALUE_COL_PLUS_SHOW_PARAM format a55

SQL> show parameter db%name

NAME                           TYPE        VALUE
------------------------------ ----------- -------------------------------------------------------
db_file_name_convert           string
db_name                        string      hawk
db_unique_name                 string      hawka
pdb_file_name_convert          string

SQL> show parameter create%dest

NAME                           TYPE        VALUE
------------------------------ ----------- -------------------------------------------------------
db_create_file_dest            string      /u01/oradata
db_create_online_log_dest_1    string      /u01/oradata
db_create_online_log_dest_2    string
db_create_online_log_dest_3    string
db_create_online_log_dest_4    string
db_create_online_log_dest_5    string

SQL> show parameter dump_dest

NAME                           TYPE        VALUE
------------------------------ ----------- -------------------------------------------------------
background_dump_dest           string      /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log
core_dump_dest                 string      /u01/app/oracle/diag/rdbms/hawka/hawk/cdump
user_dump_dest                 string      /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log
SQL>

DB Links and ADB - Dos and Don'ts

Syed Jaffar - Sat, 2020-04-04 16:00
DB Link simplifies data sharing across oracle databases. And DB link is created using the CREATE DATABASE LINK SQL command, a very simple and straight forward method. DB link creation has a different procedure on an Autonomous database in contract to typical Oracle databases. In this post, we will highlight the rules, limitations and the use of new DBMS_CLOUD_ADMIN package. Let's get into the action.

Rules and Limitations

Note some of below rules and limitations of creating DB link from ADB:

  • The target databases (non-ADB) should be configured to use TCP/IP with SSL (TCPS) authentication
  • The allowed DB port range is 1521-1525 to ensure the security. You can define the port while creating the DB link
  • ADB DB link support databases that are only accessible through a public IP or public hostname
  • One directory restricted to one wallet file. Create multiple directories to keep multiple wallet files of multiple databases
  • Supported target database versions are 11.2.0.2, 12.1.0.2, 12.2.0.1, 18c and 19c.
Creating DB link from Autonomous Database

Now let's walk through DB link creation procedure from an Autonomous database. As mentioned, you should use DBMS_CLOUD_ADMIN.CREATE_ DATABASE_LINK procedure on ADB. Below steps describe the required procedure:
  • Copy your target database wallet file (cwallet.sso) which contains target database certification to an object store
  • Using DBMS_CLOUD.CREATE_CREDENTIAL procedure, create access credentials to the object store where the target wallet file is stored.
  • Upload the target database wallet file from Object store to database directory using the DBMS_CLOUD.GET_OBJECT procedure. Refer example below:
BEGIN 
DBMS_CLOUD.GET_OBJECT(
credential_name => 'DEFF_CRED_CRDB',
object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/
namespace-string/b/bucketname/o/cwallet.sso',
directory_name => 'DATA_PUMP_DIR');
-- DATA_PUMP_DIR is the predefined/default directory. It can be another DB directory.
END;
/
  • Create credentials on ADB, refer below example:

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'DBL_CRDB',
username => 'SCOTT', -- username must be specified in CAPITAL
password => '
welcome1'
);

END;
/
Details are stored in the database in encrypted format.
You can use the query below to list the stored credentials:

SELECT credential_name, username, comments FROM all_credentials;
  • You can use below command to drop credentials from the database:
BEGIN
DBMS_CLOUD.DROP_CREDENTIAL('DBL_CRDB');
END;

    • Now we are ready to create database link from Autonomous database. Use the example below to create:
    BEGIN
    DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'ADB_TO_CRDB',
    hostname => 'adb.eu-frankfurt-1.oraclecloud.com',
    port => '1525',
    service_name => 'example_medium.
    adwc.example.oraclecloud.com',
    ssl_server_cert_dn => 'CN=
    adwc.example.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
    credential_name => 'DBL_CRDB',
    directory_name => 'DATA_PUMP_DIR');

    END;
    /
    If you are not an admin privileged user, get the permission to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK procedure.

    Now, you can use the DB link from ADB to the target database.

    To drop DB link from ADB, you can use the example below:

    BEGIN
    DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
    db_link_name => 'ADB_TO_CRDB' );

    END;
    /

    Creating DB link to ADB

    We have learned so far how to create a DB link from ADB to target database. This section will zip through DB link creation to ADB.
    As I have mentioned my previous blog entry that ADB only accepts secured client connections. To which, you will have to download client connect credentials. You can refer my previous blog post for the procedure.

    Before creating database link, ensure the following is met:
    • Download the client connect credentials
    • Unzip the downloaded file to a secure location your computer
    • Turn of GLOBAL_NAMES parameter using 'ALTER SYSTEM SET GLOBAL_NAMES=FALSE';
    • Using below example, create a db LINK ADB:
    CREATE DATABASE LINK DBL_TO_ADB 
    CONNECT TO SYEDJ IDENTIFIED BY welcome1
    USING
    '(description=(retry_count=20)(retry_delay=3)
    (address=(protocol=tcps)(port=1522)(host=example1.oraclecloud.com))
    (connect_data=(service_name=example2_high.adwc.oraclecloud.com))
    (security=(my_wallet_directory=/u01/targetwallet)
    (ssl_server_dn_match=true)
    (ssl_server_cert_dn="CN=example2.oraclecloud.com,OU=Oracle
    BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))'
    ;

    At this point in time the DB link is ready.

    In nutshell, this blog post explained the do's and don'ts of creating DB link from/to ADB.





    Smartphone: Installing lineageOS + Googles Play Store (and getting 9GB system space freed up)

    Dietrich Schroff - Sat, 2020-04-04 15:04

    After installing TWRP as bootloader i was wondering, how much effort is is to install an alternative to android on my old smartphone.

    Heimdall & ADB were in place (see TWRP posting) - so let's start!

    I downloaded the appropriate image from https://lineageos.org/ and i did the following on my smartphone:
    Click on "Wipe"
     Click on "Format Data"
     Type "Yes"
     Wait
     Step two times back and click "advanced wipe"
    Choose "Cache" and "System" and swipe to the left

    Step back to home screen and choose "Advanced"
     Swipe to the left
    and then type:

    sudo ./adb sideload ~/Downloads/lineage-16.0-20200325-nightly-a3xelte-signed.zip 

    [sudo] password for schroff:

    Total xfer: 1.02x                                                                                 
    and then REBOOT...
    (if you want a clean lineage OS)

    If you want the google play store, then go to https://opengapps.org/


    Download the appropriate package (i chose "pico" - https://github.com/opengapps/opengapps/wiki/Package-Comparison)

    Then repeat the steps on TWRP (smartphone) for sideloading and type

    sudo ./adb sideload ~/Downloads/open_gapps-arm-9.0-pico-20200403.zip

    Total xfer: 1.43x



    And after that the smartphone starts up with lineage OS and play store (To avoid problems: do not connect to mobile networks neither wifi within the startup wizard - this can result in a black screen and the wizard will never end).
    The best thing: with Samsungs stock Android about 13GB was blocked by the system. Now only 4GB from 16GB are used. So i got additional 9GB free space. Really cool!

    Connecting to an Autonomous Database using JDBC Thin client

    Syed Jaffar - Sat, 2020-04-04 08:09
    I have discussed in my earlier posts about establishing SQL * Plus and using the SQLcl tool details. Today's blog post will take you through JDBC Thin client configuration details to be able to successfully connect with an autonomous database service. Will primarily focus on mandatory JDK, JDBC versions and client connect credentials requirement details. Let's get started.

    To begin with, any Java application that uses JDBC Thin driver requires an Oracle wallet or JavaKeyStore(JKS). Luckily, these files are readily available on OCI console for download. Refer my earlier blog post to learn about downloading client connect credentials files and JavakeyStore files.

    Prerequisites for JDBC Thin Driver

    Below is the list of perquisites for JDBC Thin client connection:

    • Download Connect Credentials as explained in the previous post
    • Validate JDK version for security, if you are on less than JDK8u162
    • Check JDBC driver version
    • JDBC driver 18.3 or higher simplifies security credentials wallet connection configuration.
    • Specify the wallet directory in the JDBC connect string.
    • Include the wallet directory location in the JDBC url if no TNS_ADMIN environmental variable is set.
    Connection string url

    Part of client connect credentials downloads, you will have tnsnames.ora files which contains tns service names and connection strings. Below is an example of high level connect string entry:

    dbname_high= (description=
    (address=(protocol=tcps)(port=1522)(host=adw.example.oraclecloud.com))(connect_data=(service_name=adw_jdbctest_high.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adw.oraclecloud.com,OU=Oracle
    US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))
    JDBC 18.3 Thin Driver connection string example, for Linux platform:

    DB_URL="jdbc:oracle:thin:@dbname_high?TNS_ADMIN=/Users/test/wallet_dbname"

    Using JavaKeyStore (JKS)

    Below example shows the JDBC Thin driver connectivity using JKS to an ADB:

    DB_URL="jdbc:oracle:thin:@dbname_high?TNS_ADMIN=/Users/test/wallet_dbname"
    Subsequently, add the JKS related connection properties to ojdbc.properties file. To use SSL connectivity over Oracle Wallet, below properties should be set to ojdbc.properties files:


    # Properties for using Java KeyStore (JKS)
    oracle.net.ssl_server_dn_match=true
    javax.net.ssl.trustStore==${TNS_ADMIN}/truststore.jks
    javax.net.ssl.trustStorePassword=password
    javax.net.ssl.keyStore==${TNS_ADMIN}/keystore.jks
    javax.net.ssl.keyStorePassword=password

    Once the properties are set, compile and run using the below example:

    java –classpath ./lib/ojdbc8.jar:./lib/ucp.jar UCPSample
    To go through your firewall with HTTP proxy requirement to connect to an internet, use JDBC Thin client 18.1 or higher. So, add the following to your tnsnames.ora file to go through HTTP proxy:

    ADWC1_high =
    (description=
    (address=
    (https_proxy=proxyhostname)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=adw.example.oraclecloud.com)
    )
    (connect_data=(service_name=adwc1_high.adw.oraclecloud.com)
    )
    (security=(ssl_server_cert_dn="adw.example.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
    )
    )

    In nutshell, we have walk through over JDBC thin driver client connection to an ADB requirements and prerequisites.

    All About SSL/TLS on Load Balancer in Oracle Cloud (OCI)

    Online Apps DBA - Sat, 2020-04-04 04:29

    A Load Balancer (LB) provides automated traffic distribution from one entry point to multiple servers in VCN. What is SSL/TLS on Load Balancer? If you want to know in more details then, Check out K21Academy’s blog post at https://k21academy.com/1z099714 which covers: • Overview On SSL On LB • Configuring SSL Termination At LB • Configuring […]

    The post All About SSL/TLS on Load Balancer in Oracle Cloud (OCI) appeared first on Oracle Trainings for Apps & Fusion DBA.

    Categories: APPS Blogs

    Pages

    Subscribe to Oracle FAQ aggregator