Feed aggregator

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.

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>

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:

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!

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.

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.

Oracle has recently announced the release of Oracle E-Business Suite 19c Database. With the Database 19c certification, EBS 12.2 on-premises databases are now certified with the CDB architecture (multitenant architecture). Check out K21 Academy post at https://k21academy.com/appsdba66 which covers: • Overview Oracle Database 19c • What’s New for EBS with Oracle Database 19c • R12.x […]

The post 19c Database In Oracle EBS R12: Everything You Must Know appeared first on Oracle Trainings for Apps & Fusion DBA.

This blog post takes you through Oracle SQL Developer command line interface connectivity to an autonomous database (ADB). SQLcl is a free command line interface for Oracle database. It will allow interactive or batch execution of SQL and PL/SQL commands with in-line editing, statement completion, and command recall for a feature-rich experience capabilities. Download the tool from the below url:

I read about /e/ some days ago and i decided to install this on my old smartphone (a samsung A3).

So first: What is /e/?
https://doc.e.foundation/
So i started at

https://doc.e.foundation/devices/a3xeltexx/install
where i learned, that i have to install a custom bootloader on my smartphone. This is very good explained at /e/:

(You will find there the description how to install "heimdall" and "adb")

schroff@zerberus:~/bin$ sudo ./heimdall flash --verbose --RECOVERY ~/Downloads/twrp-3.3.0-0-a3xelte.img --no-reboot
Heimdall v1.4.2

Copyright (c) 2010-2017 Benjamin Dobell, Glass Echidna
http://www.glassechidna.com.au/

This software is provided free of charge. Copying and redistribution is
encouraged.

If you appreciate this software and you would like to support future
development please consider donating:
http://www.glassechidna.com.au/donate/

Initialising connection...
Detecting device...
      Manufacturer: "SAMSUNG"
           Product: "Gadget Serial"

            length: 18
      device class: 2
               S/N: 0
           VID:PID: 04E8:685D
         bcdDevice: 021B
   iMan:iProd:iSer: 1:2:0
          nb confs: 1

interface[0].altsetting[0]: num endpoints = 1
   Class.SubClass.Protocol: 02.02.01
       endpoint[0].address: 83
           max packet size: 0010
          polling interval: 09

interface[1].altsetting[0]: num endpoints = 2
   Class.SubClass.Protocol: 0A.00.00
       endpoint[0].address: 81
           max packet size: 0200
          polling interval: 00
       endpoint[1].address: 02
           max packet size: 0200
          polling interval: 00
Claiming interface...
Setting up interface...

Initialising protocol...
Protocol initialisation successful.

Beginning session...

Some devices may take up to 2 minutes to respond.
Please be patient!

Session begun.

Downloading device's PIT file...
WARNING: Empty bulk transfer after receiving packet failed. Continuing anyway...
PIT file download successful.

Uploading RECOVERY
0%ERROR: Failed to unpack received packet.

ERROR: Failed to receive file part response!
3%
7%
10%
14%
17%
21%
25%
28%
32%
35%
39%
42%
46%
50%
53%
57%
60%
64%
68%
71%
75%
78%
82%
85%
89%
93%
96%
100%
RECOVERY upload successful

Ending session...
Releasing device interface...

and then:
For more information take a look at https://twrp.me/

Keeping myself occupied learning all about Autonomous Database (ADB) concepts. Thanks to Oracle for the free offer to course and a certification chance. So planned to write a few important topics along the way while learning. This post will focus on autonomous database (ADB) connectivity options, downloading connection credentials and procedure to connect a ADB database using the SQL * Plus tool.

AZ-900 Certification Exam is the first step to learn and demonstrate foundational-level knowledge of Cloud services and how those services are provided with Microsoft Azure. Check out K21 Academy’s blog post at https://k21academy.com/az90013 that covers: ▪ What Is AZ-900 Certification? ▪ Azure Certification Path ▪ AZ-900 Exam Registration Steps Begin your journey towards Azure, Getting […]

The post How to Register For [AZ-900] Microsoft Azure Fundamentals Certification Exam appeared first on Oracle Trainings for Apps & Fusion DBA.

The AZ-400 Certification Exam is geared towards professionals who can design a DevOps strategy & implement DevOps development processes; dependency management; application infrastructure; and continuous integration, delivery, and feedback. Check out K21Academy’s blog post at https://k21academy.com/az40011 which covers: ▪ Who This AZ-400 Certification Is For? ▪ Exam Details & Prerequisites ▪ Types Of Questions ▪ […]

The post [AZ-400] Microsoft Azure DevOps Certification Exam: Everything You Need To Know appeared first on Oracle Trainings for Apps & Fusion DBA.

The other day a friend called me with a question on constraints. “I have a table where the combination of two columns must be unique”. My response was: “Use a Unique Constraint on these two columns”.
That should work, however…

“There is one value for the first column, where the second column doesn’t have to be unique.”
Let’s start with a simple script to play with this issue:

prompt create a simple table
create table t
( v1 number
, v2 number
)
/
prompt add a unique constraint on columns v1 and v2
alter table t add constraint uk_2cols unique (v1, v2)
/
prompt add some data
begin
  insert into t(v1, v2) values (1,1);
  insert into t(v1, v2) values (1,2);
  insert into t(v1, v2) values (2,1);
  insert into t(v1, v2) values (2,2);
  insert into t(v1, v2) values (3,1);
  commit;
end;
/

If we add a duplicate record, this will fail:

prompt try to add a duplicate, this will fail
insert into t(v1, v2) values (3,1)
/

So using a Unique Constraint like this doesn’t work. There is a solution to this using a package and some triggers to work around a mutating table problem, but there should be a solution just using SQL and the features of the Oracle Database.

So we drop the constraint, it doesn’t do what we want.

prompt drop the constraint
alter table t drop constraint uk_2cols
/

Remember, two (or more) NULL values are considered to be Unique, so I create a virtual column in the table with the following statement:

prompt add a virtual column
alter table T add v3 as (case when v1 = 3 then null else v1 || v2 end)
/

Then we can add a unique constraint on the virtual column, which now contains NULL values for the values that don’t have to be unique

prompt add a unique constraint on this virtual column
alter table t add constraint uk_2cols unique (v3)
/

If we now try the insert again, this works.

prompt now the insert works
insert into t(v1, v2) values (3,1)
/

But the insert where the combination should be unique still failes.

prompt but this one still failes
insert into t(v1, v2) values (1,1)
/

As you can see, you can solve this issue by just using the features provided by the Oracle Database.

Hello, Ask Tom Team. I have a production database running for several months. These are my connection parameters in the client to connect to the database (RAC 18c). I am using a .NET app. Min Pool Size=1;Max Pool Size=60;Connection Lifetime=18...

Hi There, This is regarding the error ora-26002 which we are facing since few days. We have one table T1 which is having an index on one of the column. This table T1 is being loaded through informatica using bulk mode option. (Without droppi...

Hello, Ask Tom Team. I am building a report using Microsoft SQL Server Reporting Services connecting to an Oracle 18c database. This report will show lots of data. I need to paginate the results, showing 50 rows per page. 1. How can I do i...

Team, we are on 18c and trying to move a table to a different table space, using the online move it got failed like this. <code>c##rajesh@PDB1> alter session set ddl_lock_timeout =300; Session altered. c##rajesh@PDB1> show parameter ddl_l...

VMware enterprise PKS 1.7 was just released. For details please review the release notes using the link below.

https://docs.pivotal.io/pks/1-7/release-notes.html



More Information

https://docs.pivotal.io/pks/1-7/index.html

I have had an interest in PL/SQL for more around 25 years. I have always liked this great language as its powerful and simple and a great tool for writing code in the database. I wrote my very first PL/SQL....[Read More]

Posted by Pete On 02/04/20 At 01:33 PM