Oracle duplication on 12c. RMAN-04006: error from auxiliary database. RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Oracle 12.1.0.2.0

RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Instance "dbname", status BLOCKED, has 1 handler(s) for this service...
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Solution to resolve these errors.

After upgrading my Oracle instance from 11.2 to 12.1 I tried to run the RMAN duplication.

I am using the Backup Duplication method, but I expect the same issues could arise with Active Duplication as well.

Normal preparation for duplication has been preformed. Our target database is in nomount state.

First step from the target database is to connect to the auxiliary using RMAN

RMAN> connect auxiliary sys/xxxxxx@DEV01;
RMAN-00571: =================================
 RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =
RMAN-00571: =================================
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

LSNRCTL> status LISTENER
Instance "dev01", status BLOCKED, has 1 handler(s) for this service...

Solution:

1. Add (UR=A) to your tnsnames.ora
2. SERVICE_NAME entry in the tnsname.ora file should reference just the target SID and not the SID.domain_name.com
3. Add the database as a static reference in the listener.ora file so it would be recognized even if the database was not started (in nomount state).

Example of tnsnames.ora
--------------------------------
UAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uat.company.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = UAT)(UR=A)
    )
  )

Add static reference in the listener.ora file

Example of listener.ora. Add the following.

SID_LIST_LISTENER =

   (SID_LIST =

     (SID_DESC =

       (GLOBAL_DBNAME = dev01)

       (ORACLE_HOME = /app/oracle/product/12.1.0)

       (SID_NAME = dev01)

     )

  )

This should resolve the inability to connect to the target as an auxiliary while it is in no mount state.

RMAN duplication will now run but there is another issue that may crop up.

Here’s my RMAN duplication output:

…

Oracle instance shut down

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 08/19/2016 13:36:41

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

RMAN>

Solution:

The SID in your listener needs to match the case in your password file. Update DEV01 to dev01.

Password file is orapwdev01.

That should do it. Good luck. I hope this was helpful to you.

Oracle duplication - backup based duplication with no source or RMAN connection

AKA Easy duplication

Duplication is easier then ever if you have backups of your source to disk that is mounted to the auxiliary database server. No connection to your source and no connection to your RMAN catalog are both positive factors.

Other duplication methods
Active Duplication
Backup Duplication from Media


Source database – The source database instance is the instance that will be duplicated.

Auxiliary database – The database instance associated with the duplicate database is called the auxiliary instance.

Prerequisites

1. You have an auxiliary (development or test) instance running
2. Both the source and the auxiliary database servers have the backup directory mounted in a fashion that allows the auxiliary database access to the source backups
3. The auxiliary database server has enough space and memory for a duplicate of the source

Let's do it.

If you are running OEM on the Auxiliary instance, shut it down.
# emctl stop dbconsole
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.

Login to your Auxiliary instance shut it down.

SQL> select name from v$database;
NAME
---------
TEST01

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Delete all archive log directories and archive log files.
cd /app/oracle/flash_recovery_area/TEST01/archivelog

# rm -rf 2013*

Add a Primary Key to an existing table that has no unique data

DBA's and Developer's don't always agree. But one thing they should agree on is the existence of a primary key on every table. Agreeing and doing are two different things and I have on an occasion found myself having to create a primary key on a table that is already populate and has no unique attributes, or combination of, to place a primary key on.  Maybe you just want to convert a concatenated primary key to a primary key on a single column that does not exist.

These are the steps I use to make a new column and make it a primary key on an existing table that already has data.

What we are going to do is add a new, unique column that will auto-populate, for our primary key.

Table name:  MY_TABLE
One Index, one foreign key constraint
No trigger or primary key on the table

1. Create a new sequence
2. Create a new table with the existing data and new populated primary key
3. Rename the foreign key and index on the current table
4. Rename the original table to old and the new table to the original name
5. Recreate the index and recreate the foreign key
6. Create a new trigger to auto-populate our new column going forward
7. Grant permission on new table 
8. Add the new PRIMARY KEY

Create a new sequence used to populate the primary key

CREATE SEQUENCE MY_TABLE_SEQ
START WITH 1
INCREMENT BY 1
MINVALUE 1
NOCACHE
NOCYCLE
NOORDER;

Create a new table with a newly populated column to be the primary key
CREATE TABLE MY_TABLE_NEW
AS
(SELECT
 MY_TABLE _SEQ.NEXTVAL
 ID
, NAME
, DATA
, SCORE
FROM MY_TABLE);

Rename the existing Foreign Key
ALTER TABLE MY_TABLE
 RENAME CONSTRAINT
MY_TABLE _FK
 TO
 MY_TABLE _FK_OLD; 

Rename the existing Index

ALTER INDEX MY_TABLE _IDX
RENAME TO
MY_TABLE _FK_OLD;

Rename the original table to OLD, the new table to the original name 
ALTER TABLE MY_TABLE
RENAME TO
MY_TABLE _OLD;

ALTER TABLE MY_TABLE _NEW
 RENAME TO
 MY_TABLE;

Recreate both the index and the Foreign key
CREATE INDEX MY_TABLE _IDX
ON MY_TABLE _IDX
(NAME);


ALTER TABLE MY_TABLE
ADD (
CONSTRAINT MY_TABLE _FK
FOREIGN KEY (NAME)
REFERENCES NAMES (NAME)
ON DELETE CASCADE
ENABLE VALIDATE);
 
Grant permission to table

GRANT DELETE, INSERT, SELECT, UPDATE ON STRUCTURE_STATS TO PUBLIC; 

Create a new trigger to auto-populate our new primary key
CREATE OR REPLACE TRIGGER SGX.BIR_MY_TABLE
BEFORE INSERT on MY_TABLE
FOR EACH ROW
BEGIN
SELECT MY_TABLE _SEQ.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END; 

Add new PRIMARY KEY
ALTER TABLE MY_TABLE
ADD CONSTRAINT MY_TABLE_PK
PRIMARY KEY
(ID)
ENABLE VALIDATE;