Oracle DBA Daily Experiences

Listener fails to process any new connections for some reason multiple listeners start up listening on the same port and running for the same service, this new listener is started by already running listener process which can be checked by looking at the parent process id of the new listener. This issue so far we have seen on 10.2.0.1 and 10.2.0.2

$ ps -ef | grep tnslsnr

oracle 8909 1 0 Sep 15 ? 902:44 /u01/oracle/db_1/bin/tnslsnr LISTENER
# the parent process id (bold) in the line below is the listener’s process id that started it
oracle 22685 8909 0 14:19:23 ? 0:00 /u01/oracle/db_1/bin/tnslsnr LISTENER

To fix this issue add the following parameter to listener.ora:
SUBSCRIBE_FOR_NODE_DOWN_EVENT_<LISTENER_NAME>=OFF

Move from single instance to RAC

1- Install CRS
2- Install new RDBMS binaries rac enable
3- Make sure there is enough space for the new online redo logs for new nodes
4- Make modifications to the pfile for RAC configuration
5- Add the listener configurations to tnsnames file in both nodes
6- Run rconfig or follow instruction on note 208375.1

Some of the steps from note and performed on the PMM RAC conversion:

GROUP# MEMBER
———- ————————————
3 /u03/oradata/PMMPRD/redo03a.log
3 /u04/oradata/PMMPRD/redo03b.log
2 /u03/oradata/PMMPRD/redo02a.log
2 /u04/oradata/PMMPRD/redo02b.log
1 /u03/oradata/PMMPRD/redo01a.log
1 /u04/oradata/PMMPRD/redo01b.log

6 rows selected.
- Add this to tnsnames.ora file in both nodes
-
LISTENER_PMM =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = calpocpmm01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = calpocpmm02-vip)(PORT = 1521))
)
alter database add logfile thread 2
group 4 (‘/u03/oradata/PMMPRD/redo04a.log’,'/u04/oradata/PMMPRD/redo04b.log’) size 500M,
group 5 (‘/u03/oradata/PMMPRD/redo05a.log’,'/u04/oradata/PMMPRD/redo05b.log’) size 500M,
group 6 (‘/u03/oradata/PMMPRD/redo06a.log’,'/u04/oradata/PMMPRD/redo06b.log’) size 500M;

alter database enable public thread 2;
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
‘/u14/oradata/PMMPRD/undotbs_02_210.dbf’ SIZE 200M ;

mkdir -p /u01/app/oracle/product/10.2.0/pmmprd/admin/PMMPRD/adump
mkdir -p /u01/app/oracle/product/10.2.0/pmmprd/admin/PMMPRD/bdump
mkdir -p /u01/app/oracle/product/10.2.0/pmmprd/admin/PMMPRD/cdump
mkdir -p /u01/app/oracle/product/10.2.0/pmmprd/admin/PMMPRD/udump
mkdir -p /u12/oradata/PMMPRD/flash_recovery_area
SQL> create spfile=’/u02/oradata/spfile_dir/spfilePMMPRD.ora’ from pfile=’/home/oracle/pfile.ora’;

Edit initPMMPRD1.ora and initPMMPRD2.ora

SPFILE=’/u02/oradata/spfile_dir/spfilePMMPRD.ora’
Add database to the CRS
srvctl add database -d PMMPRD -o $ORACLE_HOME
srvctl add instance -d PMMPRD -i PMMPRD1 -n calpmmpoc01
srvctl add instance -d PMMPRD -i PMMPRD2 -n calpmmpoc02

Today, due to a network issue the host (Solaries) lost contact with the NetApp filer and it corrupted some datafiles and redo log files.
The solution was to recreate all indexes from the corrupted datafile in a new tablespace and drop the corrupted tablespace.
The archived log process hung because it could not archive the log 464704 which got corrupted, the solution was to clear the redo log and manually switch the logs to test the fix.
1- Archived log hung because the online redo log was corrupted and could not be archived.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464713 104857600 2 NO CURRENT
2 1 464711 104857600 2 NO INACTIVE
3 1 464709 104857600 2 NO INACTIVE
4 1 464704 104857600 2 NO INACTIVE
5 1 464712 104857600 2 NO INACTIVE
6 1 464710 104857600 2 NO INACTIVE

6 rows selected.

2- When tried to clear the online redo log it wouldn’t let us do it because of the datafile corrupted as well.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4
*
ERROR at line 1:
ORA-00393: log 4 of thread 1 is needed for recovery of offline datafiles
ORA-00312: online log 4 thread 1: ‘/oracle/EPB/WISPRD/redo/redo2/redo4b.log’
ORA-00312: online log 4 thread 1: ‘/oracle/EPB/WISPRD/redo/redo1/redo4a.log’
ORA-01110: data file 281: ‘/oracle/EPB/WISPRD/data/cust_index_jul2007.dbf’

3- Found an additional clause to force the CLEAR.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4 UNRECOVERABLE DATAFILE ;

Database altered.

4- Online redo log group is now clear but keep in mind that we don’t have that archive log (464704) and recoverability is compromised at this point. We need to take a full snap backup.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464713 104857600 2 NO INACTIVE
2 1 464711 104857600 2 YES INACTIVE
3 1 464715 104857600 2 NO CURRENT
4 1 0 104857600 2 YES UNUSED
5 1 464712 104857600 2 YES INACTIVE
6 1 464714 104857600 2 NO INACTIVE
6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464719 104857600 2 YES INACTIVE
2 1 464717 104857600 2 YES INACTIVE
3 1 464715 104857600 2 YES INACTIVE
4 1 464720 104857600 2 YES ACTIVE
5 1 464718 104857600 2 YES INACTIVE
6 1 464721 104857600 2 NO CURRENT

6 rows selected.
SQL> DROP TABLESPACE CUST_INDEX_JUL2007 INCLUDING CONTENTS;

Tablespace dropped.
SQL> select distinct status from v$datafile;

STATUS
——-
ONLINE
SYSTEM

After all this a full backup was taken.

A hope that helps…