Dropping redo log memmbers - oracle 11gR2

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/appl/oradata/xdb01/CprodD/CprodD_redo03a.rdo
/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo
/appl/oradata/xdb01/CprodD/CprodD_redo02a.rdo
/appl/oradata/xdb02/CprodD/CprodD_redo02b.rdo
/appl/oradata/xdb01/CprodD/CprodD_redo01a.rdo
/appl/oradata/xdb02/CprodD/CprodD_redo01b.rdo

6 rows selected.

SQL> alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo';
alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1:
'/appl/oradata/xdb01/CprodD/CprodD_redo03a.rdo'
ORA-00312: online log 3 thread 1:
'/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo'

The above can be deleted after switching the current log file.

SQL>  alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo02b.rdo';

Database altered.

SQL> alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo01b.rdo';

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>  alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo';

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/appl/oradata/xdb01/CprodD/CprodD_redo03a.rdo
/appl/oradata/xdb01/CprodD/CprodD_redo02a.rdo
/appl/oradata/xdb01/CprodD/CprodD_redo01a.rdo

SQL>

ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error

RMAN duplicate db fails with ORA-06502

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/02/2017 10:02:21
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error

RMAN> exit

solution

1. remove db_unique_name parameter from init.ora
    and retry the duplicate. you can set the db_unique_name later once the duplicate complets fine. 

2. cleanup the catalog if required.
    remove the dbunique name related to duplicate work

oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback
> rman target / catalog rman/passs@Rmancatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 2 10:01:55 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TestPG (DBID=1734336466)
connected to recovery catalog database
re
RMAN> sync catalog;

starting full resync of recovery catalog
full resync complete

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
5548648 5548649 TestPG  1734336466       CURRENT 1          31-JAN-14
5548648 42491323 TestPG  1734336466       ORPHAN  11928117675339 12-MAY-16

RMAN> exit


Recovery Manager complete.
oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback
> sqlplus rman@Rmancatalog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 2 10:04:11 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648;

  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
   5548650    5548648 PRIMARY 31/01/14
TestPG_DG1

   5560736    5548648 STANDBY 09/03/15
TestPG_DG2

  42491321    5548648 STANDBY 12/05/16
TestPG_DG3


  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
  40708303    5548648 STANDBY
TESTSG

  46494327    5548648 STANDBY
TESTSG1

  46265758    5548648 STANDBY
TESTSG_DG1_MDR


  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
  42469535    5548648 STANDBY
TESTT4

  35485732    5548648 STANDBY
TESTT9


8 rows selected.

SQL> set linesize 200
SQL> 1
  1* select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648
SQL> /

  SITE_KEY     DB_KEY DATABAS CF_CREAT DB_UNIQUE_NAME
---------- ---------- ------- -------- ------------------------------------------------------------------------------------------------------------------------
  40708303    5548648 STANDBY          TESTSG
  46494327    5548648 STANDBY          TESTSG1
  35485732    5548648 STANDBY          TESTT9
  46265758    5548648 STANDBY          TESTSG_DG1
  42491321    5548648 STANDBY 12/05/16 TestPG_DG3
   5560736    5548648 STANDBY 09/03/15 TestPG_DG2
  42469535    5548648 STANDBY          TESTT4
   5548650    5548648 PRIMARY 31/01/14 TestPG_DG1

8 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback




Remove the db unique names which is not related to source database. These entries are created whenever we duplicate the database and it got failed.
 
> rman target / catalog rman/passs@Rmancatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 2 10:05:18 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TestPG (DBID=1734336466)
connected to recovery catalog database

RMAN> unregister db_unique_name TESTT4;

database db_unique_name is "TESTT4", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTT4 unregistered from the recovery catalog

RMAN> unregister db_unique_name TESTSG;

database db_unique_name is "TESTSG", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTSG unregistered from the recovery catalog

RMAN> unregister db_unique_name TESTSG1
2> ;

database db_unique_name is "TESTSG1", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTSG1 unregistered from the recovery catalog

RMAN> unregister db_unique_name TESTT9;

database db_unique_name is "TESTT9", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTT9 unregistered from the recovery catalog

RMAN> exit


Recovery Manager complete.
oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback
> sqlplus rman@Rmancatalog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 2 10:07:42 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL>  select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648
  2  /

  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
   5548650    5548648 PRIMARY 31/01/14
TestPG_DG1_MDR

   5560736    5548648 STANDBY 09/03/15
TestPG_DG2_HTC

  42491321    5548648 STANDBY 12/05/16
TestPG_DG3_MDR


  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
  46265758    5548648 STANDBY
TESTSG_DG1_MDR


SQL> set linesize 200
SQL> 1
  1*  select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648
SQL> /

  SITE_KEY     DB_KEY DATABAS CF_CREAT DB_UNIQUE_NAME
---------- ---------- ------- -------- ------------------------------------------------------------------------------------------------------------------------
   5548650    5548648 PRIMARY 31/01/14 TestPG_DG1
   5560736    5548648 STANDBY 09/03/15 TestPG_DG2
  42491321    5548648 STANDBY 12/05/16 TestPG_DG3
  46265758    5548648 STANDBY          TESTSG_DG1

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
oracle@oravm1[TestPG]:/

Activating physical standby database to primary /normal database

Activating the physical standby database to primary database. 

# recover process is running

SQL> !ps -ef|grep mrp
oracle   28937     1  0 Feb24 ?        00:00:38 ora_mrp0_TestSG
oracle   49454 49444  0 09:02 pts/0    00:00:00 /bin/ksh -c ps -ef|grep mrp
oracle   49456 49454  0 09:02 pts/0    00:00:00 grep mrp
# cancelling the recover process
SQL> alter database recover managed standby database cancel;

Database altered.

#checking database status

SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
TESTSG   PHYSICAL STANDBY

# completing the recovery

SQL> alter database recover managed standby database finish;

Database altered.

#checking the database role and activating the database

SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
TESTSG   PHYSICAL STANDBY

SQL> alter database activate physical standby database;

Database altered.

SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
TESTSG   PRIMARY

SQL> alter database open ;

Database altered.

#checking the database mode

SQL>  select  name,database_role,open_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
TESTSG   PRIMARY          READ WRITE