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.