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;

Create a Fast Refreshable Materialized View on a Complex Query

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

This is an example of a Fast Refreshable Materialized View on more than one table. Fast Refreshable Materialized Views are not possible on complex query's. This usually means more than one table or using functions in the SQL clause. It is possible to form a Fast Refreshable Materialized View on 2 tables albeit generally not very useful, but in trying to do so, I can demonstrate how to determine if it is at all possible with examples of using the MV_CAPBILITIES Table.

Here I will form a 2 table join and use it in a Fast Refreshable Materialized View. The first step is to create te Materialized View Log on both base tables.
 
SQL> create materialized view log on experiment_types with primary key;
SQL> create materialized view log on experiment_runs with primary key;

Create the Materialized View as FAST refresh, with PRIMARY KEY, and ON COMMIT options.

 create materialized view EXPERIMENTS_MV
 refresh
 with primary key
 fast
 on commit
 as
 select USERNAME,EXP_NAME
 from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
 where A.EXPID = B.EXPID
 order by USERNAME,EXP_NAME;
     *
ERROR at line 8:
ORA-12052: cannot fast refresh materialized view EXPERIMENTS_MV

We get an error saying it cannot be Fast Refresh. Well okay then. Instead let's create the Materialized View with no options using only the defaults. This will let us test the SQL portion of the Materialized View.

create materialized view EXPERIMENTS_MV
as
select USERNAME,EXP_NAME
from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
where A.EXPID = B.EXPID
order by USERNAME,EXP_NAME;

This works. Now let's query USER_MVIEWS to see what kind of Materialized View was created
Force means try FAST first and if this is not possible then do a COMPLETE refresh.

SQL> select mview_name,refresh_method,refresh_mode
from user_mviews
where mview_name = 'EXPERIMENTS_MV';

MVIEW_NAME         REFRESH_METHOD     REFRESH_MODE
--------------     -------------       -----------------
EXPERIMENTS_MV     FORCE                 DEMAND    

We see that our Materialized View was created as FORCE and DEMAND. Let's try and refresh it as FAST using the DBMS_MVIEW.REFRESH procedure. We pass in F for Fast as the refresh method.

SQL> exec dbms_mview.refresh('EXPERIMENTS_MV','F');
ORA-12032: cannot use rowid column from materialized view log on
"EXPERIMENT_RUNS"

Crikey! We get an error.
This error is also telling us that the Materialized View Log needs to be defined using ROWID instead of Primary Key. Before we do that let's see if it refreshes at all by using the default refresh method.

Refresh again without the REFRESH_METHOD parameter.

SQL> exec dbms_mview.refresh('EXPERIMENTS_MV');
PL/SQL procedure successfully completed.

That works. Now let's look for some help using the Materialized View Capibilities Table.

Create the mv_capabilities_table in your schema.

SQL> @?/rdbms/admin/utlxmv.sql
Table created.

Now execute dbms_mview.explain_mview to populate the mv_capabilities_table

SQL> exec dbms_mview.explain_mview(mv=>'EXPERIMENTS_MV',stmt_id=>'100');
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

Select from mv_capabilities_table:
select capability_name
,possible
,msgtxt
from mv_capabilities_table
where capability_name like 'REFRESH_FAST_AFTER%'
and statement_id = '100'
order by 1;

CAPABILITY_NAME            POSSIBLE    MSGTXT                RELATED_TEXT
-------------------------    --------    ---------------------        ----------------
REFRESH_FAST_AFTER_INSERT    N        mv log must have ROWID    EXPERIMENT_RUNS
REFRESH_FAST_AFTER_INSERT    N        mv log must have ROWID    EXPERIMENT_TYPES

The MSGTXT says that our Materialized View logs need to be ROWID Based.
Drop and recreate the Materialized View and Materialized View logs and recreate the Materialized View logs with ROWID.

SQL> drop materialized view EXPERIMENTS_MV;
SQL> drop materialized view log on EXPERIMENT_TYPES;
SQL> drop materialized view log on EXPERIMENT_RUNS;

Recreate the Materialized View Logs with rowed this time.
SQL> create materialized view log on experiment_types with rowid;
SQL> create materialized view log on experiment_runs with rowid;
Recreate Materialized View
create materialized view EXPERIMENTS_MV
as
select USERNAME,EXP_NAME
from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
where A.EXPID = B.EXPID
order by USERNAME,EXP_NAME;

Delete existing records from mv_capabilities_table.
SQL> delete from mv_capabilities_table;
SQL> commit;

SQL> exec dbms_mview.explain_mview(mv=>'EXPERIMENTS_MV',stmt_id=>'100');
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

Select from mv_capabilities_table:
CAPABILITY_NAME            POSSIBLE    MSGTXT       
-------------------------    --------    --------------------------------       
REFRESH_FAST_AFTER_INSERT    N       
the SELECT list does not have the rowids of all the detail tables

The MSGTXT now says that our Materialized View select list does not contain rowids.
Remember that the select clause in a Fast Refresh Materialized View must contain either PK or ROWID.

Again we drop and recreate the Materialized View and Materialized View logs. We recreate the Materialized View logs with ROWID and we include the ROWID in the select clause.

SQL> drop materialized view EXPERIMENTS_MV;
SQL> drop materialized view log on EXPERIMENT_TYPES;
SQL> drop materialized view log on EXPERIMENT_RUNS;
SQL> create materialized view log on experiment_types with rowid;
SQL> create materialized view log on experiment_runs with rowid;

create materialized view EXPERIMENTS_MV
as
select A.ROWID AS AROWID
, B.ROWID AS BROWID
,USERNAME
,EXP_NAME AS EXPERIMENT_NAME
from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
where A.EXPID = B.EXPID
order by USERNAME,EXP_NAME;

SQL> delete from mv_capabilities_table;
SQL> commit;

SQL> exec dbms_mview.explain_mview(mv=>'EXPERIMENTS_MV',stmt_id=>'100');
PL/SQL procedure successfully completed.

SQL> commit;

Finally we select from mv_capabilities_table one more time:

CAPABILITY_NAME                POSSIBLE    MSGTXT       
------------------------------    -----        --------------------------------       
REFRESH_FAST_AFTER_ANY_DML        Y   
REFRESH_FAST_AFTER_INSERT        Y   
REFRESH_FAST_AFTER_ONETAB_DML    Y   

Now we can refresh the Materialized View:
SQL> exec dbms_mview.refresh('EXPERIMENTS_MV','F');
PL/SQL procedure successfully completed.

Finally drop and recreate both the Materialized View and the Materialized View Logs. This time though you can create your Materialized View using the ROWID, FAST, and ON COMMIT options.

SQL> create materialized view EXPERIMENTS_MV
 refresh
 with rowid
 fast
 on commit
 as
 select A.ROWID AS EXP_TYPES_ROWID
 , B.ROWID AS EXP_RUNS_ROWID
 ,USERNAME,EXP_NAME
 from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
 where A.EXPID = B.EXPID
 order by USERNAME,EXP_NAME;

Materialized view created.

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Fast Refreshable Materialized View

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query


Simple Materialized View
Appropriate for Materialized View’s where the base tables have a small amount of changes over a period of time compared to the total number of rows in the table.
1. Create the Materialized View log.
2. Create the Materialized View as Fast Refreshable.

A Fast Refreshable Materialized View requires a Materialized View Log on the base table(s).
SQL> create materialized view log on experiment_runs;

select object_name, object_type
from user_objects where object_name like '%EXPERIMENT%';

RUPD$_EXPERIMENT_RUNS    TABLE
MLOG$_EXPERIMENT_RUNS    TABLE

create materialized view EXPERIMENTS_MV
refresh
with primary key
fast
on commit
as
select EXP_RUNID, USERNAME
from EXPERIMENT_RUNS
where SUCCESS = 'Y'
order by USERNAME;

select object_name, object_type
from user_objects where object_name like '%EXPERIMENT%';

RUPD$_EXPERIMENT_RUNS    TABLE
MLOG$_EXPERIMENT_RUNS    TABLE
EXPERIMENTS_MV        MATERIALIZED VIEW
EXPERIMENTS_MV        TABLE
Primary Key VS. ROWID
SQL> create materialized view log on experiment_runs with primary key; (This is the default).
SQL> create materialized view log on experiment_runs with rowid; (Required if there is no PK on base table).

The Primary Key parameter also applies to the Materialized View itself. The ‘with primary key’ parameter is the default option and if no primary key exists, the Materialized View must include the ‘with rowed’ parameter.

When you use create a Primary Key-Fast Refreshable Materialized View, the Primary Key MUST be part of the Materialized View select clause.

Simple Materialized View VS. Complex Materialized View
Simple Materialized View can always be Fast Refreshed. Based on a single table (usually). It is possible to create a Simple Materialized View on a complex query (2 tables).

Complex Materialized View cannot be Fast Refreshed. This includes queries with more than one table, aggregates, and functions. You can still create a Materialized View that is Complete Refresh on Commit.

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query