tns-12560 message 12560 not found

Sometimes Users Cannot access to Database You Need To check The following :

Sqlplus / as sysdba

Show parameter local_listener ;

You have to see output like this :


local_listener            (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=you-ip-address)(PORT=1521))))

  If you see Null Value Do the Following :

Alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.250.25)(PORT=1521))))‘ scope=memory ;

Or For Real Application :

Alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.250.25)(PORT=1521))))‘ scope=memory sid=’you-node-name’;

Scope = memory For Test Purpose

Sql > Alter system register ;

Now You should see database name service on listener ;

to Apply these Changes all we have to do is scope=spfile like the following :

Alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.250.25)(PORT=1521))))‘ scope=spfile ;

Or For Real Application :

Alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.250.25)(PORT=1521))))‘ scope=spfile sid=’you-node-name’;

Thank you
Osama Mustafa

RAC TO SINGLE NODE STEPS

Take RMAN backup of the production RAC database

1-
RMAN> run{
allocate channel c1 type disk format ‘/tmp/%U’;
backup database;
backup archivelog all;

backup controlfile ;
}

2-Create Pfile from RAC for single Node Using SQLPLUS :
 SQL>create pfile ” from spfile ;

3-Open pfile that you create it and modify the following parameters manually:
%dest, control_files
log_archive_dest_1
cluster_database_instances

4-Use the pfile created above to STARTUP NOMOUNT the database on the new host
$ sqlplus / as sysdba
SQL> startup nomount;

5-rman target /
A-restore controlfile from ‘/tmp/’;
OR
  restore controlfile from ‘/media/moh_1509/MEPSLIVE_20110919_762231895_3885.ctl’




B-ALTER DATABASE MOUNT ;
C.catalog backuppiece  ‘.bkp’ repeat it for all of backupset .
D.catalog backuppiece  ‘.arch’ repeat it for all of ARCHIVELOG.

E.
run
{
set newname for datafile 1 to ”;
set newname for datafile 2 to  ”;
set newname for datafile 3 to ”;
set newname for datafile 4 to  ”;
set newname for datafile 5 to  ”;
set newname for datafile 6 to  ”;
     restore database;
     switch datafile all;
}

NOTE : datafile number must be taken from the production system .

F.
sqlplus / as sysdba

alter database rename file ” to ”;
alter database rename file ” to ”;
alter database rename file ” to ”;

G.
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;

Then Delete unnecessary Redolog .

6.RMAN TARGET /
RMAN>RECOVER DATABASE ;

7.SQL> alter database open resetlogs;

If open database fail with error ORA-38856
then, Set the following parameter in the init.ora file:

_no_recovery_through_resetlogs=TRUE

8. 
SQL> select THREAD#, STATUS, ENABLED 2 from v$thread;

SQL> select group# from v$log where THREAD#=2;
SQL> alter database disable thread 2;

9.DROP REDO LOG FROM NEW SINGLE NODE BY :
 SQL> alter database clear unarchived logfile group ;  
SQL>alter database drop logfile group ;

After doing this you can remove undo tablespace for other instance 

10.To Remove it :
 SQL> show parameter undo;
SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’;
SQL> drop tablespace UNDOTBS2 including contents and datafiles; 

Now you have single node instance 🙂

Thank you
Osama Mustafa