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!

Advertisements

Script to capture and restore file permissions

Backing up file permissions is the best practice. Even extra permissions on files can mess up installed software.

Editing this post:

Thanks to zhwsh about this comment, that even does not need to be explained:

“getfacl -R /u01/app/11.2.0.4/grid > dir_privs.txt
setfacl –restore dir_privs.txt”

In any case leaving perl script that does the same as getfacl. 

Usage:

chmod 755 backup_permissions.pl

./backup_permissions.pl <Path>

Script: 

#!/usr/bin/perl -w
#
# Captures file permissions and the owner of the files
# useage : perm1.pl <path to capture permission>
#

use strict;
use warnings;
use File::Find;
use POSIX();

my (@dir) = @ARGV;
my $linecount=0 ;

#print @ARGV, $#ARGV;

if ($#ARGV < 0) {
print “\n\nOpps….Invalid Syntax !!!!\n” ;
print “Usage : ./perm1.pl <path to capture permission>\n\n” ;
print “Example : ./perm1.pl /home/oralce\n\n” ;
exit ;
}
my $logdir=$dir[0] ;
#my ($sec, $min, $hr, $day, $mon, $year) = localtime;
##my ($dow,$mon,$date,$hr,$min,$sec,$year) = POSIX::strftime( ‘%a %b %d %H %M %S %Y’, localtime);
my $date = POSIX::strftime( ‘%a-%b-%d-%H-%M-%S-%Y’, localtime);
my $logfile=”permission-“.$date;
my $cmdfile=”restore-perm-“.$date.”.cmd” ;

open LOGFILE, “> $logfile” or die $! ;
open CMDFILE, “> $cmdfile” or die $! ;
find(\&process_file,@dir);

print “Following log files are generated\n” ;
print “logfile : “.$logfile. “\n” ;
print “Command file : “.$cmdfile. “\n” ;
print “Linecount : “.$linecount.”\n” ;
close (LOGFILE) ;
close (CMDFILE) ;

sub process_file {
my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size, $atime,$mtime,$ctime,$blksize,$blocks,$username,$user,$pass,$comment,$home,$shell,$group);
my %uiduname = () ;
my %gidgname = () ;
my $filename = $File::Find::name;

#### Building uid, username hash

open (PASSWDFILE, ‘/etc/passwd’) ;

while ( <PASSWDFILE>) {
($user,$pass,$uid,$gid,$comment,$home,$shell)=split (/:/) ;
$uiduname{$uid}=$user ;
}
close (PASSWDFILE) ;

#### Building gid, groupname hash

open (GRPFILE, ‘/etc/group’) ;

while ( <GRPFILE>) {
($group,$pass,$gid)=split (/:/) ;
$gidgname{$gid}=$group ;
}
close (GRPFILE) ;

($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size, $atime,$mtime,$ctime,$blksize,$blocks) = stat(“$filename”);
# printf “%o %s %s %s\n”, $mode & 07777, $uiduname{$uid}, $gidgname{$gid}, $filename ;
printf LOGFILE “%o %s %s %s\n”, $mode & 07777, $uiduname{$uid}, $gidgname{$gid}, $filename ;
printf CMDFILE “%s %s%s%s %s\n”, “chown “,$uiduname{$uid}, “:”, $gidgname{$gid}, $filename ;
printf CMDFILE “%s %o %s\n”, “chmod “,$mode & 07777, $filename ;
# printf “%o %s %s %s\n”, $mode & 07777, $uiduname{$uid}, $gidgname{$gid}, $filename ;
$linecount++ ;
}

Note:

The above script generates restore-perm-<timestamp>.cmd file.

When you want to restore permissions make this file executable and run:

chmod 755 restore-perm-<timestamp>.cmd

./restore-perm-<timestamp>.cmd