Remember your dreams

Remember your dreams
Remember your dreams

Wednesday, April 18, 2012

RMAN unregister and register after duplication

If you were using the RMAN catalog for the your auxiliary instance before duplication you will need to reregister it. The newly created duplicated instance now has a new DBID. This example will demonstrate how to reincarnate your database or simple cut to the chase and register a database with an rman catalog.

In my example I just completed an Active Duplication of my production instance to a development instance I call dev311.

Log onto the development server and dev311 database.

Using rman connect to the target and catalog.

% rman target / catalog login/xxxx@vcatdb

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 12 11:01:44 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: dev311 (DBID=3811178510)
connected to recovery catalog database

Note your new DBID. Now Query the catalog for the incarnation of dev311. You see that the DBID is not the same as your newly duplicated database.

RMAN> list incarnation of database dev311;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1841905798 1841905799 DEV311 3805185786 CURRENT 9804844295329 02-DEC-10

You will notice that the DBID for the registered version of dev311 is different than your current DBID of the dev311 database. The DBID has changed so we need to unregister and register our new DBID.

Exit RMAN and log in without connecting to the target, connect to the catalog only.

Set your DBID to the dev311 instance already known to the catalog and unregister the old instance.

# rman
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Feb 24 08:01:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN>

RMAN> connect catalog login/passwd@catdb;
connected to recovery catalog database

RMAN> set dbid 3805185786; # this is the old DEV311 dbid.
executing command: SET DBID
database name is "DEV311" and DBID is 3805185786

RMAN> unregister database;
database name is "DEV311" and DBID is 3805185786
Do you really want to unregister the database (enter YES or NO)? y
database unregistered from the recovery catalog

RMAN> exit

Now exit RMAN and reconnect to the target and catalog.

RMAN> connect target / catalog login/passwd@catdb;

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 12 11:12:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEV311 (DBID=3811178510)
connected to recovery catalog database

Register the new database instance.

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation of database dev311;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1992522226 1992522424 DEV311 3811178510 PARENT 1 02-DEC-10
1992522226 1992522227 DEV311 3811178510 CURRENT 9864858231054 12-APR-12

RMAN> list backup;
specification does not match any backup in the repository

Since you recreated the registration in the catalog you need to also reconfigure your default settings. Some of my important configuration are the following:

RMAN> run {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 35 DAYS;
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO '%F';
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 4;
CONFIGURE BACKUP OPTIMIZATION ON;
}

RMAN> crosscheck archivelog all;


Now backup your database.

Tuesday, April 17, 2012

Recreating the OEM .

Recreating the Oracle Enterprise Manager (OEM) for your newly duplicated database.
Enterprise Manager Grid Control - Version: 10.1.0.2 to 11.2.0.2 - Release: 10.1 to 11.2

If there is a change in SID, DBID, or hostname for the database that has been restored then you need to recreate the dbconsole.

Refer How To Reconfigure DB Control After a Hostname, Domain name or Listener Change Has Occurred On The Server
(MOS Doc ID 293678.1) Information in this document applies to any platform.

This is the process:

Stop OEM

# emctl stop dbconsole

Drop the existing OEM installation.

# cd $ORACLE_HOME
# cd ./sysman/admin/emdrep/bin/
#./RepManager servername.domain.com 1526 dev311 -action drop

Enter SYS user's password :
Enter repository user name : sysman
Getting temporary tablespace from database...
.
.
.
Dropping Repos User ... Done.
Dropping Roles/Synonymns/Tablespaces ... Done.
Dropped Repository Successfully.

Install the OEM

# cd $ORACLE_HOME/bin
./emca -config dbcontrol db -repos create

STARTED EMCA at Apr 12, 2012 2:26:40 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: dev311
Database Control is already configured for the database dev311
You have chosen to configure Database Control for managing the database dev311
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]:y

Listener ORACLE_HOME [ /app/oracle/product/11.2.0 ]:
Password for SYS user: xxxx
Password for DBSNMP user: xxxx
Password for SYSMAN user: xxxxx
Email address for notifications (optional): email@company.com
Outgoing Mail (SMTP) server for notifications (optional): smtp-server.com

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

You have specified the following settings

Database ORACLE_HOME ................ /app/oracle/product/11.2.0.2

Local hostname ................ servername.com
Listener ORACLE_HOME ................ /app/oracle/product/11.2.0.2
Listener port number ................ 1526
Database SID ................ dev311
Email address for notifications ............... email@company.com
Outgoing Mail (SMTP) server for notifications ............... smtp-server.com

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 12, 2012 2:28:25 PM oracle.sysman.emcp.EMConfig perform
.
.
.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 12, 2012 2:34:42 PM

I like to finish up with a stop and start of the emctl services.

# emctl stop dbconsole

# emctl start dbconsole

Using my browser I can access OEM and review my newly cloned database.


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.


Proactive Oracle DBA

This is a series of posts. I am working this to share some of the many scripts I schedule to automatically run to alert me of any current o...