Duplicate a Database Using RMAN

create duplicate database using rman

You want to use RMAN to create a duplicate database on the same server by using RMAN backups.

My Source Database Name is MYTEST

Target (duplicate) Database name is MYDB

Step 1: Configure Listener.ora and tnsnames.ora files

tnsnames.ora file

MYTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest)
)
)

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
)

listener.ora file

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mytest)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mytest)
)
(SID_DESC =
(GLOBAL_DBNAME= mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mydb)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)

Step 2: create pfile for target database from source database

SQL> create pfile from spfile;

File created.

Step 3: modified parameter file for target database (duplicate) here log_file_name_convert and db_file_name_convert and control file location is important, further your choice.

*.audit_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/adump’
*.compatible=’10.2.0.1.0′
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’mydb’
*.control_files=’/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl’
*.db_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.log_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.db_recovery_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=mydbXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Step 4: Create password file

[oracle@localhost ~]$orapwd file=orapwmydb password=oracle entries=5

Step 5: start your target database nomount stage

[oracle@localhost ~]$ export ORACLE_SID=mydb
[oracle@localhost ~]$ rlwrap sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Tue May 25 18:22:09 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> startup nomount pfile=/home/oracle/oracle/product/10.2.0/db_1/dbs/initmydb.ora
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> exit

Step 6: From source database

SQL> alter system switch logfile;

System altered.

SQL>

Step 7: Backup database and archivelog Source database

[oracle@localhost root]$ rlwrap rman target sys/azar@mytest

Recovery Manager: Release 10.2.0.1.0 – Production on Tue May 25 19:56:10 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: MYTEST (DBID=2419076664)

RMAN> backup database plus archivelog;

Starting backup at 25-MAY-10
current log archived

input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/sysaux01.dbf
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-10
channel ORA_DISK_1: finished piece 1 at 25-MAY-10

(skipped)

Starting Control File and SPFILE Autobackup at 25-MAY-10
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_25/o1_mf_s_719956316_5zr01f85_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAY-10

Step 8: Connect auxiliary database

RMAN> connect auxiliary sys/oracle@mydb;

connected to auxiliary database: MYDB (not mounted)

Step 9: Create duplicate database.

RMAN> duplicate target database to mydb;

Starting Duplicate Db at 25-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
set until scn 470310;
set newname for datafile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf”;
set newname for datafile 2 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
set newname for datafile 3 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
set newname for datafile 4 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-MAY-10
using channel ORA_AUX_DISK_1

skipping datafile 1; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
skipping datafile 2; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
skipping datafile 3; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
skipping datafile 4; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 25-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

contents of Memory Script:
{
set until scn 470310;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 25-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-MAY-10

contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1

contents of Memory Script:
{
set newname for tempfile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=1 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=2 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf recid=3 stamp=719956593

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 25-MAY-10

RMAN>

Step 10: My duplicate database succesfully created.

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS
————
OPEN

SQL> select name from V$database;

NAME
———
MYDB

SQL>

Cloning Oracle Database - Cold Cloning, Hot Cloning

Cloning Oracle Database - Cold Cloning, Hot Cloning

Clone an Oracle Database using Cold Physical Backup
Database Name: troy Source Database side: (Troy database) Cold Backup

Steps: 1. Get the file path information using below query

Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

2. Parameter file backup If troy database running on spfile

Create pfile=’/u01/backup/inittroy.ora’ from spfile;
If database running in pfile using os command to copy the pfile and placed in backup path.

3.Taken the control file backup Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

4.Shutdown immediate

5.Copy all the data files/log files using os command & placed in backup path.

6.Startup the database.

Clone Database side: (Clone database) Database Name: Clone

Clone Database Steps:

1.Create the appropriate folder in corresponding path & placed the backup files in corresponding folder.(bdump,udump,create,pfile,cdump,oradata)

2.Change the init.ora parameter like control file path, dbname, instance name etc...

3.Create the password file using orapwd utility. (Database in windows we need to create the service id using oradim utility)

4.Startup the Database in NOMOUNT stage.

5.Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.

Change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.

CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS FORCE LOGGING NOARCHIVELOG MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 453 LOGFILE GROUP 1 '/U01/oradata/clone/redo01.log' SIZE 200M, GROUP 2 '/U01/oradata/clone/redo02.log' SIZE 200M, GROUP 3 '/U01/oradata/clone/redo03.log' SIZE 200M DATAFILE '/U01/oradata/clone/system01.dbf', '/U01/oradata/clone/undotbs01.dbf', '/U01/oradata/clone/users01.dbf', CHARACTER SET WE8ISO8859P1; Note: placed the script in sql prompt. Now controlfile created. 6.Now open the database. Alter database open resetlogs; Note: Check the logfile, datafiles & instance status **********************************************************************************
Clone an Oracle Database using Hot Physical Backup

Database Name: troy Database must be in Archive log mode.

Source Database side: (Troy database)

Hot Backup Steps:

1.Get the file path information using below query.

Select tablespace_name, file_name from dba_data_files order by 1;

2. Parameter file backup
If troy database running on spfile Create pfile

Create file=’/u01/backup/inittroy.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.

3.Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path. (Refer below example)

4.Once copied the datafile, release the tablespace from begin backup mode to end backup

5.Repeat the steps 1-3 for all your tablespaces.

6.Taken the controlfile backup
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

7.Backup all your archive log files between the previous backup and the new backup as well.

Example: steps: 2.Alter tablespace system begin backup;

To ensure the begin backup mode tablespace using below query

Select * from v$backup; (refer the Change#, Time column)

3. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf à using os command to copy the datafile.

4. Alter tablespace system end backup;

To ensure the begin backup mode tablespace using below query

Select * from v$backup; Clone Database side: (Clone database)

Database Name: Clone

Clone Database Steps:

1.Create the appropriate folder in corresponding path & placed the backup files in corresponding folder.(bdump,udump,create,pfile,cdump,oradata)

2.Change the init.ora parameter like control file path, dbname, instance name etc...

3.Create the password file using orapwd utility.Database in windows we need to create the service id using oradim utility)

4.Startup the Database in NOMOUNT stage.

5.Create the control file for cloning database. Using backup controlfile trace to generate the create controlfile scripts. Change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.

CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS FORCE LOGGING NOARCHIVELOG MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 453 LOGFILE GROUP 1 '/U01/oradata/clone/redo01.log' SIZE 200M, GROUP
2 '/U01/oradata/clone/redo02.log' SIZE 200M, GROUP 3 '/U01/oradata/clone/redo03.log' SIZE 200M DATAFILE '/U01/oradata/clone/system01.dbf', '/U01/oradata/clone/undotbs01.dbf', '/U01/oradata/clone/users01.dbf', CHARACTER SET WE8ISO8859P1;

Note: placed the script in sql prompt. Now controlfile created.

6.Recover the database using controlfile.

Recover database using backup controlfile until cancel;

7.Now open the database. Alter database open resetlogs;

Note: Check the logfile, datafiles status.

Check Versions of Oracle Applications Components

Check Versions of Oracle Applications Components

Help-> About Oracle Applications from any of the forms sessions.

Or

Connect as apps user
select release_name from apps.fnd_product_Groups;

Version of Applications Forms (fmb,fmx) or reports (rdf)

To find the version of any oracle applications files
strings -a ICQTYED.fmx | grep Header

Alternatively you can also use the adident command
adident Header ICQTYED.fmx

Version of a Java class File
To find the version of a java class file
$ strings | grep '$Header'

Version of JDBC

In your middle tier, edit the jserv.properties file located in the iAS_ ORACLE_HOME/Apache/Jserv/etc directory
Locate the wrapper.classpath that is pointing to the jdbc zip file
/d01/oracle/viscomn/java/jdbc14.zip

Or
http://.:port/OA_HTML/jsp/fnd/aoljtest.jsp
Look for String ” JDBC driver version” under Connection String


Version of Apache
Go to the $iAS/Apache/Apache/bin directory and enter the following command:
$ httpd -version

Version Of The OA Framework
To find out the version of your Oracle appplication Framework
Option 1
http://.:port/OA_HTML/OAInfo.jsp

Option 2
adident Header %FND_TOP%/html/OA.jsp
adident Header %OA_HTML%/OA.jsp

Note: OA.jsp should be of the same version in both the places


Version of Application Product or Patch Set Level.

One of the most common things you will asked by your support engineer is the version or commonly known as the patch set level of your Oracle Applications product, while or after raising your service request. You can query this by logging on to your application database as the apps user.

select patch_level from fnd_product_installations where patch_level like '%AD%';
Version of Discoverer
cd $APPL_TOP/admin
grep -I s_disco_ver_comma **xml

Version of OJSP

Log in to the application server as the applmgr user
cd $OA_HTML
edit the jtflogin.jsp file to add the following line
OJSP Version: <%= application.getAttribute("oracle.jsp.versionNumber") %>
clear your cache and bounce your apache server
soruce the jtflogin.jsp from your browser

http://[your web server]:[your port]/OA_HTML/jtflogin.jsp
OJSP Version: 1.1.3.5.2


Version of JSP
Log on to your HTTP Server node, and change to the OA_HTML directory.
Using a text editor, create a file called test.jsp with only the following line:
<%= application.getAttribute("oracle.jsp.versionNumber") %>

Access this JSP from a web browser, using the URL:
http://[your web server]:[your port]/OA_HTML/test.jsp

Version of Portal
select fnd_oracle_schema.getouvalue(’PORTAL’) from dual;

FND_ORACLE_SCHEMA.GETOUVALUE(’PORTAL’)
——————————————————————————–
PORTAL30 <= to retrieve the portal user select version from PORTAL30.wwc_version$; VERSION ——————————————————————————– 3.0.9.8.1 or begin execute immediate 'select fnd_oracle_schema.getouvalue(''PORTAL'') from dual' into portal_user; begin execute immediate 'select user_name from fnd_user where user_name like upper('''||portal_user||''')' into portal_user_name; if portal_user is not null then execute immediate 'select version from '||portal_user||'.wwc_version$' into portal_ver; check_message := ' [PASS] Your Applications database contains Portal version: '||portal_ver; dbms_output.put_line(check_message); end if; exception when no_data_found then null; end; exception when others then null ; end; Version of XML Parser You can find out the version of your XML Parser using the following query SQL> select WF_EVENT_XML.XMLVersion() XML_VERSION
2 from sys.dual;
XML_VERSION
--------------------------------------------------------------------------------
Oracle XDK Java 9.0.4.0.0 Production

Version of XML Publisher

To check weather XML publisher is installed or not you can query the FND_PRODUCT_INSTALLATIONS table or you can lookup the
reports in Oracle Applications Manager. You can find out the version for your XML publisher from the output of your report or from MetaInfo.class file.

$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.

Version of WorkFlow

You can find out the version of your workflow using the following query
SQL> select TEXT Version from WF_RESOURCES
2 where TYPE = 'WFTKN' and NAME = 'WF_VERSION';
VERSION
--------------------------------------------------------------------------------
2.6.0

Version of Oracle Login Server

begin
execute immediate 'select fnd_oracle_schema.getouvalue(''LOGINSERVER'') from
dual' into sso_user;
begin
execute immediate 'select user_name from fnd_user where user_name like
upper('''||sso_user||''')' into
sso_user_name;
if sso_user is not null then
execute immediate 'select version from '||sso_user||'.wwc_version$' into
sso_ver;
check_message := ' [PASS] Your Applications database contains Login
Server version: '||sso_ver;
dbms_output.put_line(check_message);
end if;
exception
when no_data_found then
null;
end;
exception
when others then
null ;
end;