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

Creating a Complete 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

Creating a Complete Refreshable Materialized View
 
Before you can create a Materialized View you need CREATE MATERIALIZED VIEW and CREATE TABLE system privileges.

Appropriate for Materialized View’s where the base tables have a significant amount of row changes. Also required when Fast Refresh is not possible.

create materialized view EXPERIMENTS_MV

refresh

complete

on demand

as

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

Query user_mviews

select mview_name

,refresh_method

,refresh_mode

,fast_refreshable

from user_mviews

where mview_name = ‘EXPERIMENTS_MV’;

MVIEW_NAME        REFRESH_METHOD    REFRESH_MODE      FAST_REFRESHABLE

---------------   -------------     ----------- ----------------

EXPERIMENTS_MV    COMPLETE          DEMAND            NO

How to refresh a Materialized View:

Use the refresh procedure of the DBMS_MVIEWpackage.

Passing in one parameter:

(The refresh method will be the default refresh method for

that Materialized View)

The name of the Materialized View and the refresh method.

SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV');

Passing in two parameters:

The name of the Materialized View

The refresh method.

SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV','C');

You can include more than one Materialized View name

with a comma delimited string in quotes.

For example:

SQL> exec dbms_mview.refresh('MV_1, MV_2,MV_3’,'CFC');

If you exclude the refresh type parameter then the default

refresh method is used. You will get an error if you try to

use F for Fast Refresh and your Materialized View is not

set up as a Fast Refreshable Materialized View.

Refresh Types:

C = Complete

F = Fast

? = Force

SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV','?');

Force: Performs a FAST if possible, otherwise it performs a

complete refresh.

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

What is a Materialized View?

More on Materialized Views
  Materialized Views

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

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

What is a Materialized View?

Let’s start by looking at the differences between a View and a Materialize View.

A View is a logical table based on one or more tables or views.

Logical table meaning that the query is run at the time you select from the view.

A Materialized View is like a regular view except that the initial query has already been run and the results are actually stored in a physical table. When you select from a Materialized View you are only querying the underlying table where the results are already stored.

View – SQL query executed when you query the view.

Materialized View – Select directly from a base table where the SQL query results are already stored.

Materialized Views VS. Row/Result Cache

• Materialized View – The results stored in a table. Refreshed completely or only the DML transactions to the base table on commit or at periodic intervals.
• Result Cache – The results stored in memory.  Refreshed anytime the underlying data is changed.

 Complete Refresh VS. Fast Refresh

• Complete Refresh simply reloads the entire Materialized View from scratch.
• Fast Refresh, once created, only applies the DML changes to the Materialized View.

 Materialized View Log

• Fast Refresh requires a Materialized View Log be created prior to creating the Materialized View. The Materialized View Log is the table that stores all the DML changes that take place on the base table for the Materialized View. The Materialized View Log is self maintained and once the records are applied to the Materialized View they are automatically purged from the Materialized View log.

Refresh Mode

On Demand, On Commit, or Never.

• On Demand:  Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package REFRESH.

• On Commit: Refresh occurs automatically when a transaction that modified one of the Materialized View's base tables commits.  

For example, let’s say you have query that provides a sum of minutes and totals by user and experiment. You have an application that is running this query and because of the sum and aggregate it continues to take longer as time goes by.

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

Let’s transform this query into a table that can be queried.

CREATE TABLE EXPERIMENTS

AS

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

You put into place a nightly process to truncate and refresh it each day.

TRUNCATE TABLE EXPERIMENTS;

INSERT INTO EXPERIMENTS...

Now each day the users have sub-second query results by selecting from EXPERIMENTS.  The only problem is that the data is only up to date through the prior day.

This is similar to a Materialized View, but a Materialized View can not only be automated, it can greatly enhance this process.  A Materialized View has additional features, many which we will discuss, that a regular View does not have. 



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

Materialized Views

Materialized View technology has been available since Oracle version 7. Originally it was called snapshot technology and soon became known as Materialized View Replication. There are several reasons you might want to use Materialized Views in your environment. First, it’s a great way to offload data to a data warehouse or a separate reporting database.  Also you can use them to improve query performance by periodically computing and storing the results of aggregated data and complex queries. 

The following is based on my white paper for a session I am presenting at Collaborate in Denver, Co the week of April 7th. 

This blog is broken into several specific  topics 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