DBT-06103 The port (1,521) is already used

Thanks Tornike Kupatadze for this testing case!

Problem:

During my OCA class, after successful 19c database software installation, we were creating a database using dbca and got the following error:

If the listener had already been configured we would have had an error DBT-06103 The port (5,500) is already used while configuring EM express. But still, the solution is the same.

We have checked and the port was not used:

# netstat -a |grep 1521

Reason:

The hostname is not reachable. The reason in our case was that /etc/hosts did not contain entries about this server.

Solution:

Qualify the hostname into the /etc/hosts:

After adding the above entry, we were able to continue.

ORA-15120: ASM file name ‘ORA-27090: Unable to reserve kernel resources f’ does not begin with the ASM prefix character

Problem:

The customer created 36 databases on the same server and while creating the 37th using dbca got the following error:

Reason:

fs.aio-max-nr value was set too low in /etc/sysctl.conf. In general, value 3145728 that was set in their case, suits many environments, but if the number of databases on the server increases then this parameter should be adjusted accordingly.

Solution:

The formula used while calculating the value for this parameter is the following:

aio-max-nr = no of process per DB * no of databases * 4096

In their case, the number of processes per DB was 1000, the number of databases that planned to be created was 80. Based on the above value should be:

aio-max-nr = 327680000
  1. Add/update value in /etc/sysctl.conf:
# vim /etc/sysctl.conf

fs.aio-max-nr = 327680000


2. Run /sbin/sysctl -p to immediately enforce the changes:

# sysctl -p 

Delete already created files and recreate the database, it will succeed this time.

Create database using DBCA in VNC

In this tutorial, we will configure VNC and create a database using DBCA.

Source: https://support.flashgrid.io/hc/en-us…

Steps that are necessary to run DBCA via Xming (Flashgrid-enabled clusters)

1. Remove immutable flag from /etc/ssh/sshd_config file:

# chattr -i /etc/ssh/sshd_config

2. Change the following in /etc/ssh/sshd_config:

From:

#X11UseLocalhost yes

To:

X11UseLocalhost no

4. Readd immutable flag to /etc/ssh/sshd_config file:

# chattr +i /etc/ssh/sshd_config

4. Restart sshd service:

# systemctl restart sshd

5. Start Xming on your computer and reconnect to the server via putty with X11 enabled.

6. Check current DISPLAY settings:

$ echo $DISPLAY
192.168.1.2:10.0

7. Copy Xauthority file from fg to oracle

$ sudo cp /home/fg/.Xauthority /home/oracle/.Xauthority
$ sudo chown oracle:oinstall /home/oracle/.Xauthority

8. Switch to oracle user and export DISPLAY variable, assign the same value that was returned from fg user (from where you run sudo su – oracle):

$ sudo su – oracle
$ export DISPLAY=192.168.1.2:10.0

9. Run DBCA:

$ dbca

Create RAC database using DBCA silent mode

Real World Scenario: 

Previously, we had a vacancy on Senior DBA position. Some of our candidates had >15 years of experience in database administration.

So for testing their knowlege we created lab. There were already installed grid and database softwares, shared disks were present and diskgroups were already created.

The first task was to create RAC database in silent mode using DBCA.  They had an option to use the internet during the exam. But unfortunatelly they have not managed to do that.

So I decided to write the simple version of the script:

dbca -silent \
-createDatabase \
-templateName General_Purpose.dbc \
-gdbName orcl  \
-sid orcl  \
-SysPassword MyPassword123 \
-SystemPassword MyPassword123 \
-emConfiguration NONE \
-redoLogFileSize 2048  \
-recoveryAreaDestination FRA \
-storageType ASM \
-asmSysPassword MyPassword123 \
-diskGroupName DATA \
-characterSet AL32UTF8 \
-nationalCharacterSet AL32UTF8 \
-automaticMemoryManagement true \
-totalMemory 2536  \
-databaseType MULTIPURPOSE \
-nodelist rac1,rac2

Copying database files
1% complete
3% complete
9% complete
15% complete
21% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
85% complete
94% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log” for further details.

dbca deleteDatabase removes listener alias from tnsnames.ora

I have two databases ORCL, MYDB. Each of them has LOCAL_LISTENER  set to listener alias NODEFQDN that is described in tnsnames.ora.

[oracle@rac1 ~]$ cat /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora

NODEFQDN =
(ADDRESS = (PROTOCOL = TCP)(Host = rac1.mydomain.com)(Port = 1522))

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = maritest-scan.mydomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)))

MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = maritest-scan.mydomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb)))

[oracle@rac1 ~]$ sqlplus mari@ORCL
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> show parameter local_listener

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string NODEFQDN

[oracle@rac1 ~]$ sqlplus mari@MYDB
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> show parameter local_listener

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string NODEFQDN

I have deleted ORCL database using dbca :

[oracle@rac1 ~]$ dbca -silent -deleteDatabase -sourceDB orcl
Enter SYS user password:

Connecting to database 9% complete 14% complete 19% complete 23% complete 28% complete 33% complete 38% complete 47% complete
Updating network configuration files 48% complete 52% complete
Deleting instances and datafiles 66% complete 80% complete 95% complete 100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/orcl.log” for further details.

Checked tnsnames.ora and see that NODEFQDN alias is deleted:

[oracle@rac1 ~]$ cat /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora

MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = maritest-scan.mydomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb)))

Problem is that MYDB still has LOCAL_LISTENER=NODEFQDN. It means when I restart MYDB database it will not automatically be registered with the listener because tns does not contain NODEFQDN anymore.

Identified that, deletion of this entry depends on LOCAL_LISTENER parameter. If it is set to this alias then during db deletion that entry  is also deleted(unfortunatelly, dbca does not consider if that entry is used by other dbs) . If the parameter is empty or has the value :  (ADDRESS = (PROTOCOL = TCP)(Host = rac1.mydomain.com)(Port = 1522)) then entry stays in tnsnames.ora after db deletion.

To prevent dbca delete that entry from tnsnames.ora even LOCAL_LISTENER is set to NODEFQDN. There exist one trick:

In tnsnames.ora single entry can have multiple aliases, this is not docummented but seems we have a lot of hidden features:

Example:

alias1,alias2, alias3 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=service_name)))

You can have blank spaces between aliases or not have, just you must separate them by commas.

So in our case we can write like this:

DONOTDELETE,NODEFQDN =
(ADDRESS = (PROTOCOL = TCP)(Host = rac1.mydomain.com)(Port = 1522))

It is up to you what will be the first alias 🙂 You may write DBCADOTTOUCH ))

Each alias is resolvable:

[oracle@rac1 ~]$ tnsping NODEFQDN
..
Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(Host = rac1.mydomain.com)(Port = 1522))
OK (0 msec)

[oracle@rac1 ~]$ tnsping DONOTDELETE
..
Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(Host = rac1.mydomain.com)(Port = 1522))
OK (0 msec)

After database deletion this entry stays in tnsnames.ora

Good Luck!

Could not validate ASMSNMP password due to…During DB creation

If you are creating database using DBCA and came across the following error:

Screenshot-Database Configuration Assistant

Do the following:

bash-3.2$ . oraenv
ORACLE_SID = [RDBMS] ? +ASM
The Oracle base for ORACLE_HOME=/u02/app/11.2.0/grid is /u01/app/oracle
bash-3.2$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 21 18:59:20 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Automatic Storage Management option

SQL> create user ASMSNMP identified by testpas1;
create user ASMSNMP identified by testpas1
*
ERROR at line 1:
ORA-01990: error opening password file ‘/u02/app/11.2.0/grid/dbs/orapw’

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Automatic Storage Management option
bash-3.2$ echo $ORACLE_HOME
/u02/app/11.2.0/grid
bash-3.2$ cd /u02/app/11.2.0/grid/dbs/
bash-3.2$ orapwd file=orapw+ASM password=testpas1 entries=2
bash-3.2$ ls
ab_+ASM.dat  hc_+ASM.dat  init+ASM.ora  init.ora  orapw+ASM  spfile+ASM.ora
bash-3.2$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 21 19:01:20 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Automatic Storage Management option

SQL> create user ASMSNMP identified by testpas1;

User created.

SQL> grant sysdba to ASMSNMP;

Grant succeeded.

That’s it. Re-run DBCA.

Could not validate ASMSNMP password due to…During DB creation

If you are creating database using DBCA and came across the following error:

Screenshot-Database Configuration Assistant

Do the following:

bash-3.2$ . oraenv
ORACLE_SID = [RDBMS] ? +ASM
The Oracle base for ORACLE_HOME=/u02/app/11.2.0/grid is /u01/app/oracle
bash-3.2$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 21 18:59:20 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Automatic Storage Management option

SQL> create user ASMSNMP identified by testpas1;
create user ASMSNMP identified by testpas1
*
ERROR at line 1:
ORA-01990: error opening password file ‘/u02/app/11.2.0/grid/dbs/orapw’

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Automatic Storage Management option
bash-3.2$ echo $ORACLE_HOME
/u02/app/11.2.0/grid
bash-3.2$ cd /u02/app/11.2.0/grid/dbs/
bash-3.2$ orapwd file=orapw+ASM password=testpas1 entries=2
bash-3.2$ ls
ab_+ASM.dat  hc_+ASM.dat  init+ASM.ora  init.ora  orapw+ASM  spfile+ASM.ora
bash-3.2$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 21 19:01:20 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Automatic Storage Management option

SQL> create user ASMSNMP identified by testpas1;

User created.

SQL> grant sysdba to ASMSNMP;

Grant succeeded.

That’s it. Re-run DBCA.