Oracle 11g, Active duplication.

Oracle Active Duplication, database duplication and cloning.
Oracle Enterprise Edition 11.2.0.2
Linux x86_64, RH 5

I have a previous post describing how I used duplication in 10g to clone my production database to a development instance on a development server. Now in 11g we have the option of using Active Duplication to achieve the same results. Active Duplication is more streamline and so much more simple than previous methods of duplication. This is my example of using Oracle 11gR2 Active Duplication to clone my production database instance to a development instance on the development database server.

Active Duplication does not require a backup of your database. This is a big plus! From the production database server we will login and push the production instance across the network to the development server using RMAN. The downside is that there is some utilization of the production database as well as increased network traffic. A small price to pay to alleviate the headaches the former method of duplication gave me.

There are two methods of Active duplication. Pfile and Spfile. I much prefer the Pfile method (and you will see why), but I will describe the differences and give examples of both methods.

In this example the production instance is named prod311 and our cloned copy will be called dev311.

Terms:
Target – The production database or database you are going to clone. I am using prod311. 
Auxiliary – The duplicated database. I am using dev311.

On the DEVELOPMENT server:
The auxiliary database server must have a matching directory structure to that of the target database server. If your auxiliary environment already exists then the duplication process is much simpler. If not then you must create the environment and directory structure and the simplest way to do that is by creating a new database on the auxiliary server, in this case it would be called dev311. Just be sure all the datafile directories exist (irregardless of the tablespace or datafile names) as they do on the target with the exception of the SID.

For example the prod311 database will have a paths of
/u01/oracle/oradata/prod311
/app/oracle/diag/rdbms/prod311/prod311/trace

and dev311 instance will have
/u01/oracle/oradata/dev311
/app/oracle/diag/rdbms/dev311/dev311/trace

If this is a repeat of the duplication process and your dev311 instance already exists then you are good to go. 

spfile method
Move the current pfile and create a new pfile with only the db_name parameter.
% more $ORACLE_HOME/dbs/initdev311.ora
db_name='dev311' 

pfile method
Before shutting down the auxiliary database, obtain a current pfile if you do not have one already.

SQL> create pfile from spfile;
File created. 

For the pfile method use your current pfile and add the following parameters: 

*.db_file_name_convert='prod311','dev311' 

*.log_file_name_convert='prod311','dev311' 

Both pfile and spfile methods:
Everything else applies to both the pfile and spfile methods with the exception of the RMAN script which I describe further down.

Oracle Net Connectivity:
Establish Oracle Net Connectivity to the Auxiliary instance (dev311) from the Target Host (production, prod311, server). Edit the tnsnames.ora on the Target (prod311) server and add the dev311 (Auxiliary) database.

Now test from the Target -
% tnsping dev311
TNS Ping Utility for Linux: Version 11.2.0.2.0
Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Auxiliary-Server.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev311.Auxiliary-Server.com)))
OK (320 msec)

Password file on the Auxiliary server for dev311:
Password file must exist for the Auxiliary database and it MUST MATCH THE TARGET sys user:

Insure there is a password file for the auxiliary instance on the remote server. If not then create a password file on the auxiliary server for the auxiliary database. Be sure the password matches the sys password on your production instance since this is the database we will be cloning.
   
On the Auxiliary server
% cd $ORACLE_HOME/dbs
% orapwd file=orapwdev password=sys_same_on_prod311

Shutdown the auxiliary database.

Delete archive logs from the dev311 instance:
There will likely be existing archive log files on disk for the dev311 instance. Having any existing archive logs for your dev311 environment will cause problems during the duplication.
Delete all archive log folders and/or archive logs for the dev311 instance on the auxiliary server.

% cd $ORACLE_BASE/flash_recovery_area/dev311/archivelog/
% rm -rf *

Move the existing spfile to a new name:
# mv spfiledev311.ora spfiledev311.old 

Restart in nomount with the updated pfile:
SQL> shutdown immediate;
SQL> quit 

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 12 09:21:34 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.

Start the auxiliary database in nomount using the pfile:

SQL> startup nomount pfile='/app/oracle/product/11.2.0.2/dbs/initdev311.ora'
ORACLE instance started.

On the target prod311 database server start RMAN. Connect to the target and auxiliary. 
The RMAN catalog is not required for Active Duplication.

# rman
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 12 02:31:44 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Note: Do not use the syntax: connect target /
Specify it as: connect target sys/password@SID

Connect to the target (the database you are cloning) and include the alias.
RMAN> connect target sys/xxxxx@prod311;
connected to target database: prod311 (DBID=132331749)

Connect to the auxiliary, dev311, and include the alias.
RMAN> connect auxiliary sys/xxxxx@dev311;
connected to auxiliary database: dev311 (not mounted)

Notice the auxiliary database in not mounted.

Increase the parallelism to 4 (or more).
RMAN> configure device type disk parallelism 4;

Executing the RMAN duplication command:

The first 2 RMAN examples are using the spfile method. The 3rd example, and simplest, is using the pfile method.

This is the basic spfile example:

RMAN> duplicate target database to dev311
2> from active database
3> db_file_name_convert 'prod311','dev311'
4> spfile
5> parameter_value_convert 'prod311','dev311'
6> set log_file_name_convert 'prod311','dev311'
7> nofilenamecheck;
This spfile method example demonstrates how to modify parameters from your target to your auxiliary database:

RMAN> duplicate target database to dev311
2> from active database
3> db_file_name_convert 'prod311','dev311'
4> spfile
5> parameter_value_convert 'prod311','dev311'
6> set log_file_name_convert 'prod311','dev311'
7> set memory_max_target '10536091648'
8> set memory_target '9462349824'
9> set db_flashback_retention_target '1440'
10> set db_recovery_file_dest_size '104857600'
11> nofilenamecheck;

Finally, saving the best for last, using the pfile method
Pfile Method.

RMAN> duplicate target database to dev
2> from active database
3> nofilenamecheck;

RMAN> exit

Recovery Manager complete.

On the newly duplicated database -
I like to shutdown the new cloned database and start it again.


If you used the pfile method make a new spfile.

SQL> create spfile from pfile;
File created. 

If you used the spfile method then create a new pfile.

SQL> create pfile from spfile;
File created. 

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

Total System Global Area 1.0489E+10 bytes
Fixed Size 2235656 bytes
Variable Size 6811550456 bytes
Database Buffers 3623878656 bytes
Redo Buffers 51380224 bytes
Database mounted.
Database opened.

SQL> select name, created, version_time, open_mode from v$database;

NAME CREATED VERSION_T OPEN_MODE
--------- --------- --------- --------------------
DEV311 12-APR-12 12-APR-12 READ WRITE

SQL> quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

If you were using the RMAN catalog for the dev311 instance before duplication you will need to reincarnate it. The newly created dev311 instance (from prod311) now has a new DBID.
Log onto the development server and dev311 database.

RMAN unregister and register after duplication.

Now backup your database.

If you were using OEM you will need to drop and recreate it for the newly duplicated database.
The following post describes how to recreate OEM after duplication,
How to recreate OEM.

Unlock locked statistics

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at “SYS.DBMS_STATS”, line 23154

ORA-06512: at “SYS.DBMS_STATS”, line 23205…

 
To unlock lock statistics for a table execute the following procedure:

exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER', 'TABLE_NAME');

Read my post about how to prevent statistics from becoming locked after expdp impdp

Preventing locked Statistics after expdp impdp

Cannot gather table statistics after data pump import impdp

exec dbms_stats.gather_table_stats(ownname=>'USER1',tabname=>'EMP');

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at “SYS.DBMS_STATS”, line 23154

ORA-06512: at “SYS.DBMS_STATS”, line 23205…

To unlock table Statistics see the following post:
Unlocking Table Statistics

I ran into this issue when I was exporting a schema from one database to import to another database.

I was not importing the rows only the objects and noticed the statistics came over. When trying to gather new statistic I received an error saying that the Statistics were locked. To prevent the statistic from being locked after importing I used the EXCLUDE STATISTICS parameter in the export.

This way the statistics are ignored and will not be locked on the new database.

In this example I want the entire schema with the exception of certain tables. I do not want Grants, rows, or statistics. Use the EXCLUDE STATISTICS parameter to prevent your statistics from being locked once you complete the import.

expdp_user1_tables.par

 

USERID=

DUMPFILE=user1_tables

LOGFILE=user1_tables

SCHEMAS=USER1

EXCLUDE=TABLE:"IN ('CONTRACT_TYPES','TIME_CODES','LAB_BOOK_ACCESS')",GRANT, TABLE_DATA

EXCLUDE=STATISTICS

Run the expdp to export the schema.

The following on one line:

dpdir> expdp parfile=expdp_user1_tables.par DIRECTORY=DATA_PUMP_DIR

ftp the dump file,  user1_tables.dmp, to the new database server.

impdp_user1_tables.par

USERID=

LOGFILE=imp_user1_tables

DUMPFILE=user1_tables

 dpdir> impdp parfile=imp_user1_tables.par DIRECTORY=DATA_PUMP_DIR

For more information on using Data Pump see the following post:

Using Data Pump 10g

Scheduler and data pump expdb

Oracle Enterprise Edition 11.2.0.2
Linux x86_64, RH 5

I would like to routinely export some or all of my database as part of my DR strategy. I have always used crontab to call my expdp script on a routine basis. Now I want to change that from using crontab to using Oracle Scheduler.

First I will review the shell script and parfile I use. Then I will use Oracle Scheduler to schedule the execution of this shell script.

This is my full export but with subtle modifications I also export specific schemas on a more frequent basis. I am using csh for this shell script.

This is expdp_full.sh

#!/bin/csh
# set the environment
setenv ORACLE_HOME /app/oracle/product/11.2.0.2
setenv ORACLE_SID grims
setenv ORACLE_BASE /app/oracle
setenv LOGFILE expdp_full.log
setenv DUMPFILE expdp_full.dmp
setenv DMPDIR /scripts/expdp

# expdp does not like files it wants
# to create lying around so I will clean
# any up just in case.

# check for existing logfile and
# remove if found.
if ( -e ${DMPDIR}/${LOGFILE} ) then
rm ${DMPDIR}/${LOGFILE}
endif

# check for existing dump file and
# remove if found.
if ( -e ${DMPDIR}/${DUMPFILE} ) then
rm ${DMPDIR}/${DUMPFILE}
endif

# change to the working directory
chdir ${DMPDIR}

# call expdp and use the full path to it.
/app/oracle/product/11.2.0.2/bin/expdp parfile=expdp_full.par DUMPFILE=${DUMPFILE} LOGFILE=${LOGFILE} DIRECTORY=DMPDIR

# once the expdp is complete zip it to save space.
# You can use the compress command in UNIX.
zip ${DUMPFILE}.`date +%m%d`.Z ${DUMPFILE}

# Get the log status for email.
set STATUS=`grep Job ${DMPDIR}/${LOGFILE}`

# mail results.
mailx -s "${ORACLE_SID} on `uname -n` FULL export '$STATUS'" email@company.com < ${DMPDIR}/${LOGFILE}

# delete the existing dump file. Zip does not remove it.
if ( -e ${DMPDIR}/${DUMPFILE} ) then
rm ${DMPDIR}/${DUMPFILE}
endif

# rename the log file to preserve it.
if ( -e ${DMPDIR}/${LOGFILE} ) then
mv ${DMPDIR}/${LOGFILE} ${DMPDIR}/${LOGFILE}.`date +%m%d`
endif
exit


The par files. This is both the full expdp and another with specific schemas.
expdp_full.par
USERID="/ as sysdba"
FULL=Y

expdp_users.par
USERID="/ as sysdba"
SCHEMAS=SCOTT,HR,APPS

Of course you can stop here and use crontab to execute expdp_full.sh, but I am going to now use the Oracle Scheduler.

First I will create the scheduled job to run every morning at 2:05am. Once it runs successfully for a few days and I like the out put I will update the schedule to run this task once a week.

From sqlplus enter the new scheduled job.

BEGIN
dbms_scheduler.CREATE_JOB (
job_name => 'expdp_full',
job_type => 'EXECUTABLE',
job_action => '/oracle/scripts/expdp/expdp_full.sh',
start_date => to_date('02/14/2011 14:00:00','mm/dd/yyyy hh24:mi:ss'),
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=5;BYSECOND=0',
end_date => null,
enabled => TRUE,
comments => 'expdp full database');
END;

To update the frequency interval to once a week, Friday morning at 2:05am:

BEGIN
sys.dbms_scheduler.disable( '"SYS"."EXPDP_FULL"' );
sys.dbms_scheduler.set_attribute( name => '"SYS"."EXPDP_FULL"', attribute => 'repeat_interval', value => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=2;BYMINUTE=5;BYSECOND=0');
sys.dbms_scheduler.enable( '"SYS"."EXPDP_FULL"' );
END;

Here are some useful queries to examine the scheduler jobs.

SQL> SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS where job_name = 'EXPDP_FULL';
JOB_NAME STATE
------------------------------ ---------------
EXPDP_FULL SCHEDULED

SQL> SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;
no rows selected

The following will show the history of a specific job and whether it succeeded or not. Just because the job succeeded does not mean the script ran without errors. The next query is an example where this first query said SUCCEEDED yet the script failed to run.

SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name,
job_class, operation, status FROM USER_SCHEDULER_JOB_LOG
WHERE job_name = 'EXPDP_FULL' ORDER BY log_date;

Show more detail about an job history. This is the output before I gave the full path in my shell script to extdp.

SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name, status,
SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO
FROM user_scheduler_job_run_details
WHERE job_name = 'EXPDP_FULL'
ORDER BY log_date;

14-FEB-11 14:02:02 EXPDP_FULL SUCCEEDED STANDARD_ERROR="expdp: Command not found

Cannot find backups when restoring database to new host.

Restore database to different host.

Oracle 11gR2. 11.2.0.1.0
Linux RH5
NetBackup 6.5.5

You may want to restore your production database to a different host, for example, to the development host database server, for testing purposes. This allows you to test your current backups and rehearse the necessary steps required in the event of a real emergency or hardware failure on the production host. In the event that you cannot rebuild the production environment in a reasonable time frame whether waiting on parts or technical expertise, this may be the answer to getting your production back up and running in hours compared to days.

Regardless of your reasons, it is a great method of testing backups and your restoration capabilities.

* RECOMMENDATION *
Rehearse, document, and practice your ability to recover the database on a regular basis. This should be part of your regular monthly tasks. When disaster strikes you will be glad you have routinely rehearsed these procedures.

I am using a development environment identical to my production environment (I know, nice eh?). Identical versions of Oracle are on both severs and my production specific directories already exist on my development server.

If your production directory structure does not exist on the development server then restore those folders from a backup of the production server. You can create them, but it is much easier to just restore them.

* IMPORTANT *
Be sure to delete any prod archive log folders on the dev server that were restored from the production server. This is important because you do not want this information cataloged during the restore (it will prevent you from finding the desired backup sets to restore). Our assumption is a total failure of the production sever so the only archive logs we want have been included in our last backup.

In this example I will restore my production instance, prod, to the development database, dev, server. I will shutdown the dev instance although you can opt to modify your memory parameters (modifying Oracle memory parameters to run both prod and dev on the same server).

-- On the development server. --
1. Recreate your production directory structure.
2. Delete any production archive log file directories.
3. Create a prod password file.\
4. Shut down emctl for dev.
% emctl stop dbconsole
Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.

Shutdown the DEV instance
% sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 3 08:38:12 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

ORA-24247: network access denied by access control list (ACL) - Enabling UTL_MAIL

I first came across this issue when migrating from version 10 to 11gR2. It continues to rear its ugly head even as I migrate to version 12.2.0.1 causing my my email alert package to no longer work.

I use a custom mail package to send email to users and alerts to the DBA. 
Executing returns the following error:
ORA-24247: network access denied by access control list (ACL).

What is this ACL business all about? It all started as a new feature in 11g and now just a feature in 12c. Oracle likes to call it Fine-Grain Access to External Network Services.

I am going to focus on my need to get the utl_mail.send procedure working again. This is how to resolve the issue and more on enabling UTL_MAIL.

This original information I used to resolve this came from the 11gR2 documentation, but as I recently discovered still holds true in 12.2.0.1. For the 11gR2 documentation, including writing a custom mail package of your own, look to the following Oracle documentation.
Oracle® Database Security Guide
11g Release 2 (11.2)
Part Number E16543-02

If this link does not work for you,
Google for "Verifying Security Access with Auditing"
Search for "Install and Configure the UTL_MAIL PL/SQL Package" within the document.
You may see an error like the following:

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2

or

ERROR at line 1:
ORA-24247: network access denied by
access control list (ACL)

Let's start by testing the utl_mail.send procedure.

SQL> begin
utl_mail.send(
sender => 'hackworthho@lilly.com',
recipients => 'hackworthho@lilly.com',
subject => 'Subject',
message => 'Test Message');
end;
/
utl_mail.send(
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'UTL_MAIL.SEND' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
For starters the utl_mail package is not installed.
UTL_MAIL is not installed in by default 11g and 12c.
You must install it manually.

Install and Configure the UTL_MAIL PL/SQL Package
% sqlplus / as sysdba
SQL> @?/rdbms/admin/utlmail.sql
SQL> @?/rdbms/admin/prvtmail.plb
If are not going to be sys then be sure to grant execute to utl_mail package to the user you are going to be executing the mail send procedure as. In this example I'll use HOWARD and the user HOWARD will own the custom mail package. Replace user HOWARD with your desired user.
GRANT EXECUTE ON SYS.UTL_MAIL TO HOWARD;

Now determine your smtp outgoing mail server. Once you have this you need to configure the database parameter set smtp_out_server.
Set the SMTP_OUT_SERVER parameter
SQL> alter system set smtp_out_server = smtp server:port scope=both;
As in
SQL> alter system set smtp_out_server = 'smtp-server.com:25’ scope=both;
System altered.
Now you need to create and configure the ACL (Access Control List).
Configuring the ACL (Access Control List) File for Network Services.

Before you can use PL/SQL network utility packages such as UTL_MAIL, you must configure an access control list (ACL) file that enables fine-grained access to external network services. To configure an access control list for the e-mail alert.

Start by creating the ACL
SQL> begin
dbms_network_acl_admin.create_acl (
acl => 'utl_mail.xml',
description => 'Enables mail to be sent',
principal => 'HOWARD',is_grant => true,
privilege => 'connect');
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Assign the access control list to the outgoing SMTP network host for your e-mail server.

SQL> begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_mail.xml',
host => 'your smtp-server.com',
lower_port => 25);
end;
/

PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

Finally grant permission to use it,
SQL> begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_mail.xml',
principal => 'HOWARD',
is_grant => TRUE,
privilege => 'connect');
end;
/

Let's try the utl_mail send package again and see if it works.

SQL> begin
utl_mail.send(
sender => 'hackworthho@lilly.com',
recipients => 'hackworthho@lilly.com',
subject => 'Subject',
message => 'Test Message from HOWARD');
end;
/
PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.
Looks like we are back in business and I did receive an email.


To Drop the ACL and start over issue the drop ACL command:
SQL> begin
DBMS_NETWORK_ACL_ADMIN.drop_acl(acl=>'utl_mail.xml');
end;
/
SQL> commit;

Commit complete.
Here are some useful queries you can use to verify the information on your database:

SELECT host,lower_port,upper_port,acl,DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'HOWARD', 'connect'),
1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE FROM dba_network_acls
WHERE host IN (SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('your smtp server.com')))
ORDER BY DBMS_NETWORK_ACL_UTILITY.domain_level(host) desc, lower_port, upper_port;

SELECT acl,principal,privilege,is_grant,TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;

select host, lower_port, upper_port, acl,DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid,null,'connect') GRANTED from dba_network_acls;