Thursday, November 18, 2010

Database Cloning

Cloning :-

Hot Backup Cloning:-

Target dbname = tomig
clone dbname = clntomig


1. Take the hot backup of target database, and backup of controlfile trace and pfile.

2. Create the directory structure for the clone database.

3. Copy the backups to the clone db and place the files to the appropirate directories.

i.e. Place the db files under oradata/clntomig folder and pfile to the
admin/clntomig/pfile directory.

4. Modify the clone db pfile parameters accordingly.

1. File location of trace files, dump file, controlfiles.
2. db_name = clntomig

IF you are doing the cloning on the same server then add the below parameters, else if your cloning on a different server with the same directory structure then the below parameters are not required.

1. db_file_name_convert = '/u01/app/oracle/oradata/tomig','/u01/app/oracle/oradata/clntomig'
2. log_file_name_convert = '/u01/app/oracle/oradata/tomig','/u01/app/oracle/oradata/clntomig'


5. Create the password file for the clone db.

unix :- $ orapwd file=$ORACLE_HOME/dbs/orapwclntomig password=sys entries=10

Windows :- > orapwd file=E:\oracle\product\10.2.0\db_1\database\PWDclntomig.ora entries=10

6. Create a New instance if the OS is windows.

>oradim -new -sid clntomig

7. Modify the Controlfile trace as below and save it as an sql script file. (controlrecreate.sql)

CREATE CONTROLFILE SET DATABASE "CLNTOMIG" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/clntomig/redo01.rdo',
'/u01/app/oracle/oradata/clntomig/redo02.rdo'
) SIZE 20M,
GROUP 2 (
'/u01/app/oracle/oradata/clntomig/redo03.rdo',
'/u01/app/oracle/oradata/clntomig/redo04.rdo'
) SIZE 20M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/clntomig/system01.dbf',
'/u01/app/oracle/oradata/clntomig/undo01.dbf',
'/u01/app/oracle/oradata/clntomig/sysaux01.dbf',
'/u01/app/oracle/oradata/clntomig/users01.dbf'
CHARACTER SET US7ASCII
/


8. Perfrom the following steps.


$ export ORACLE_SID=stdtomig
$ sqlplus / as sysdba


SQL> create spfile from pfile = '/u01/app/oracle/oradata/clntomig/init;


SQL> startup nomount;

SQL> @controlrecreate.sql

SQL> select status from V$instance;

STATUS
------
MOUNTED

Note :- In order to recover the database upto date, it has to be applied
logs of upto current log sequence. As per the below it will ask
for the log sequence to be applied. While it ask for the file
enter the name of the archived log of that particular log sequence
(of target database) as below, Once the logs upto current log
sequence is applied then enter CANCEL when it ask for the log
sequence and open the databsae with resetlogs option.

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 214245 generated at 11/18/2010 10:35:25 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_36_%u_.arc
ORA-00280: change 214245 for thread 1 is in sequence #36


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/tomig/online_archive_logs/1_36_735307345.dbf

ORA-00279: change 234921 generated at 11/19/2010 08:17:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_37_%u
_.arc
ORA-00280: change 234921 for thread 1 is in sequence #37
ORA-00278: log file
'/u01/app/oracle/oradata/tomig/online_archive_logs/1_36_735307345.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/tomig/online_archive_logs/1_37_735307345.dbf
ORA-00279: change 236329 generated at 11/19/2010 08:55:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_38_%u_.arc
ORA-00280: change 236329 for thread 1 is in sequence #38
ORA-00278: log file
'/u01/app/oracle/oradata/tomig/online_archive_logs/1_37_735307345.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/tomig/online_archive_logs/1_38_735307345.dbf
ORA-00279: change 236442 generated at 11/19/2010 09:00:33 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_39_%u_.arc
ORA-00280: change 236442 for thread 1 is in sequence #39
ORA-00278: log file
'/u01/app/oracle/oradata/tomig/online_archive_logs/1_38_735307345.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/tomig/online_archive_logs/1_39_735307345.dbf
ORA-00279: change 238628 generated at 11/19/2010 10:24:55 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_40_%u
_.arc
ORA-00280: change 238628 for thread 1 is in sequence #40
ORA-00278: log file
'/u01/app/oracle/oradata/tomig/online_archive_logs/1_39_735307345.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.


SQL> select status from V$instance;

STATUS
------
OPEN

SQL> select name from V$database;

NAME
---------
CLNTOMIG


SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/clntomig/system01.dbf
/u01/app/oracle/oradata/clntomig/undo01.dbf
/u01/app/oracle/oradata/clntomig/sysaux01.dbf
/u01/app/oracle/oradata/clntomig/users01.dbf

SQL> SELECT * FROM v$LOG;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 20971520 2 YES UNUSED 0
2 1 1 20971520 2 NO CURRENT 236443 19-NOV-10




Cold Backup Cloning:-


Target dbname = tomig
clone dbname = clntomig


1. Shutdown the target database and take the cold backup of target database
backup of controlfile trace and pfile.

2. Create the directory structure for the clone database.

3. Copy the backups to the clone db and place the files to the appropirate directories.

i.e. Place the db files under oradata/clntomig folder and pfile to the
admin/clntomig/pfile directory.

4. Modify the clone db pfile parameters accordingly.

1. File location of trace files, dump file, controlfiles.
2. db_name = clntomig

IF you are doing the cloning on the same server then add the below parameters, else if your cloning on a different server with the same directory structure then the below parameters are not required.

1. db_file_name_convert = '/u01/app/oracle/oradata/tomig','/u01/app/oracle/oradata/clntomig'
2. log_file_name_convert = '/u01/app/oracle/oradata/tomig','/u01/app/oracle/oradata/clntomig'


5. Create the password file for the clone db.

unix :- $ orapwd file=$ORACLE_HOME/dbs/orapwclntomig password=sys entries=10

Windows :- > orapwd file=E:\oracle\product\10.2.0\db_1\database\PWDclntomig.ora entries=10

6. Create a New instance if the OS is windows.

>oradim -new -sid clntomig

7. Modify the Controlfile trace as below and save it as an sql script file. (controlrecreate.sql)

CREATE CONTROLFILE SET DATABASE "CLNTOMIG" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/clntomig/redo01.rdo',
'/u01/app/oracle/oradata/clntomig/redo02.rdo'
) SIZE 20M,
GROUP 2 (
'/u01/app/oracle/oradata/clntomig/redo03.rdo',
'/u01/app/oracle/oradata/clntomig/redo04.rdo'
) SIZE 20M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/clntomig/system01.dbf',
'/u01/app/oracle/oradata/clntomig/undo01.dbf',
'/u01/app/oracle/oradata/clntomig/sysaux01.dbf',
'/u01/app/oracle/oradata/clntomig/users01.dbf'
CHARACTER SET US7ASCII
/


8. Perfrom the following steps.

[oracle@rhel clntomig]$ ls -l
total 1419756
-rw-r--r-- 1 oracle oinstall 731 Nov 19 10:16 con.sql
-rw-r--r-- 1 oracle oinstall 1309 Nov 19 08:46 initclntomig.ora
-rw-r----- 1 oracle oinstall 524296192 Nov 19 10:15 sysaux01.dbf
-rw-r----- 1 oracle oinstall 524296192 Nov 19 10:15 system01.dbf
-rw-r----- 1 oracle oinstall 78651392 Nov 17 14:58 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov 19 10:15 undo01.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov 19 10:15 users01.dbf

[oracle@rhel clntomig]$ export ORACLE_SID=clntomig
[oracle@rhel clntomig]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 19 10:20:09 2010

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

Connected to an idle instance.

SQL> create spfile from pfile = '/u01/app/oracle/oradata/clntomig/initclntomig.ora';

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes

SQL> @con.sql;

Control file created.

SQL> select status from V$instance;

STATUS
---------
MOUNTED

Note :- In order to recover the database upto date, it has to be applied
logs of upto current log sequence. As per the below it will ask
for the log sequence to be applied. While it ask for the file
enter the name of the archived log of that particular log sequence
(of target database) as below, Once the logs upto current log
sequence is applied then enter CANCEL when it ask for the log
sequence and open the databsae with resetlogs option.

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 214245 generated at 11/18/2010 10:35:25 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_36_%u_.arc
ORA-00280: change 214245 for thread 1 is in sequence #36


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/tomig/online_archive_logs/1_36_735307345.dbf

ORA-00279: change 234921 generated at 11/19/2010 08:17:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_37_%u
_.arc
ORA-00280: change 234921 for thread 1 is in sequence #37
ORA-00278: log file
'/u01/app/oracle/oradata/tomig/online_archive_logs/1_36_735307345.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/tomig/online_archive_logs/1_37_735307345.dbf
ORA-00279: change 236329 generated at 11/19/2010 08:55:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_38_%u_.arc
ORA-00280: change 236329 for thread 1 is in sequence #38
ORA-00278: log file
'/u01/app/oracle/oradata/tomig/online_archive_logs/1_37_735307345.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/tomig/online_archive_logs/1_38_735307345.dbf
ORA-00279: change 236442 generated at 11/19/2010 09:00:33 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_39_%u_.arc
ORA-00280: change 236442 for thread 1 is in sequence #39
ORA-00278: log file
'/u01/app/oracle/oradata/tomig/online_archive_logs/1_38_735307345.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/tomig/online_archive_logs/1_39_735307345.dbf
ORA-00279: change 238628 generated at 11/19/2010 10:24:55 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_40_%u
_.arc
ORA-00280: change 238628 for thread 1 is in sequence #40
ORA-00278: log file
'/u01/app/oracle/oradata/tomig/online_archive_logs/1_39_735307345.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

SQL> select status from V$instance;

STATUS
------
OPEN

SQL> select name from V$database;

NAME
---------
CLNTOMIG


SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/clntomig/system01.dbf
/u01/app/oracle/oradata/clntomig/undo01.dbf
/u01/app/oracle/oradata/clntomig/sysaux01.dbf
/u01/app/oracle/oradata/clntomig/users01.dbf


RMAN Cloning - Same Server:-

Target dbname = tomig
clone dbname = clntomig


1. Take the rman backup of the target database and pfile backup.

2. Create the directory structure for the clone database.

3. Modify the clone db pfile parameters accordingly.

1. File location of trace files, dump file, controlfiles.
2. db_name = clntomig

IF you are doing the cloning on the same server then add the below parameters, else if your cloning on a different server with the same directory structure then the below parameters are not required.

1. db_file_name_convert = '/u01/app/oracle/oradata/tomig','/u01/app/oracle/oradata/clntomig'
2. log_file_name_convert = '/u01/app/oracle/oradata/tomig','/u01/app/oracle/oradata/clntomig'


4. Create the password file for the clone db.

unix :- $ orapwd file=$ORACLE_HOME/dbs/orapwclntomig password=sys entries=10

Windows :- > orapwd file=E:\oracle\product\10.2.0\db_1\database\PWDclntomig.ora entries=10

5. Create a New instance if the OS is windows.

>oradim -new -sid clntomig

6. Update the Entries in the tnsnames.ora file and listener.ora
according.

CLNTOMIG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel.manzoor.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clntomig)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = clntomig)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
)



7. Check whether the clond db is reaching by using tnsping utility.

[oracle@rhel clntomig]$ tnsping clntomig

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-NOV-2010 12:15:41

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

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rhel.manzoor.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = clntomig)))
OK (0 msec)


8. Perform the below steps.


[oracle@rhel clntomig]$ export ORACLE_SID=clntomig
[oracle@rhel clntomig]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 19 12:16:35 2010

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

Connected to an idle instance.

SQL> create spfile from pfile = '/u01/app/oracle/oradata/clntomig/initclntomig.ora';

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Note:- Once the database instance is started we need to exit from the session.

[oracle@rhel admin]$ export ORACLE_SID=clntomig
[oracle@rhel admin]$ rman target sys/admin@tomig nocatalog auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Nov 19 12:20:11 2010

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

connected to target database: TOMIG (DBID=2728202513)
using target database control file instead of recovery catalog
connected to auxiliary database: CLNTOMIG (not mounted)


RMAN> list backup of database;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 298.69M DISK 00:01:40 19-NOV-10
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20101119T120829
Piece Name: /u01/app/oracle/flash_recovery_area/TOMIG/backupset/2010_11_19/o1_mf_nnndf_TAG20101119T120829_6gd6v61w_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 241342 19-NOV-10 /u01/app/oracle/oradata/tomig/system01.dbf
2 Full 241342 19-NOV-10 /u01/app/oracle/oradata/tomig/undo01.dbf
3 Full 241342 19-NOV-10 /u01/app/oracle/oradata/tomig/sysaux01.dbf
4 Full 241342 19-NOV-10 /u01/app/oracle/oradata/tomig/users01.dbf
5 Full 214245 18-NOV-10 /u01/app/oracle/oradata/tomig/test01.dbf

RMAN> duplicate target database to clntomig;

Starting Duplicate Db at 19-NOV-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
set until scn 241409;
set newname for datafile 1 to
"/u01/app/oracle/oradata/clntomig/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/clntomig/undo01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/clntomig/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/clntomig/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/clntomig/test01.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

executing command: SET NEWNAME

Starting restore at 19-NOV-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/clntomig/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/clntomig/undo01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/clntomig/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/clntomig/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/clntomig/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TOMIG/backupset/2010_11_19/o1_mf_nnndf_TAG20101119T120829_6gd6v61w_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TOMIG/backupset/2010_11_19/o1_mf_nnndf_TAG20101119T120829_6gd6v61w_.bkp tag=TAG20101119T120829
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 19-NOV-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLNTOMIG" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/clntomig/redo01.rdo', '/u01/app/oracle/oradata/clntomig/redo02.rdo' ) SIZE 20 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/clntomig/redo03.rdo', '/u01/app/oracle/oradata/clntomig/redo04.rdo' ) SIZE 20 M REUSE
DATAFILE
'/u01/app/oracle/oradata/clntomig/system01.dbf'
CHARACTER SET US7ASCII


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=735481417 filename=/u01/app/oracle/oradata/clntomig/undo01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=735481417 filename=/u01/app/oracle/oradata/clntomig/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=735481417 filename=/u01/app/oracle/oradata/clntomig/users01.dbf

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

executing command: SET until clause

Starting recover at 19-NOV-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=158 devtype=DISK
datafile 5 not processed because file is read-only

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=41
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=42
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TOMIG/backupset/2010_11_19/o1_mf_annnn_TAG20101119T121105_6gd703kh_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TOMIG/backupset/2010_11_19/o1_mf_annnn_TAG20101119T121105_6gd703kh_.bkp tag=TAG20101119T121105
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
archive log filename=/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_41_6gd7r2wm_.arc thread=1 sequence=41
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_41_6gd7r2wm_.arc recid=2 stamp=735481435
archive log filename=/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_42_6gd7r2g1_.arc thread=1 sequence=42
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/flash_recovery_area/CLNTOMIG/archivelog/2010_11_19/o1_mf_1_42_6gd7r2g1_.arc recid=1 stamp=735481434
media recovery complete, elapsed time: 00:00:02
Finished recover at 19-NOV-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 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLNTOMIG" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/clntomig/redo01.rdo', '/u01/app/oracle/oradata/clntomig/redo02.rdo' ) SIZE 20 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/clntomig/redo03.rdo', '/u01/app/oracle/oradata/clntomig/redo04.rdo' ) SIZE 20 M REUSE
DATAFILE
'/u01/app/oracle/oradata/clntomig/system01.dbf'
CHARACTER SET US7ASCII


contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/clntomig/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/clntomig/undo01.dbf";
catalog clone datafilecopy "/u01/app/oracle/oradata/clntomig/sysaux01.dbf";
catalog clone datafilecopy "/u01/app/oracle/oradata/clntomig/users01.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/clntomig/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/clntomig/undo01.dbf recid=1 stamp=735481457

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/clntomig/sysaux01.dbf recid=2 stamp=735481457

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/clntomig/users01.dbf recid=3 stamp=735481457

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=735481457 filename=/u01/app/oracle/oradata/clntomig/undo01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=735481457 filename=/u01/app/oracle/oradata/clntomig/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=735481457 filename=/u01/app/oracle/oradata/clntomig/users01.dbf

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

database opened

contents of Memory Script:
{
catalog clone datafilecopy "/u01/app/oracle/oradata/clntomig/test01.dbf";
switch clone datafile 5 to datafilecopy
"/u01/app/oracle/oradata/clntomig/test01.dbf";
#online the readonly tablespace
sql clone "alter tablespace TEST online";
}
executing Memory Script

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/clntomig/test01.dbf recid=4 stamp=735481472

datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=735481472 filename=/u01/app/oracle/oradata/clntomig/test01.dbf

sql statement: alter tablespace TEST online
Finished Duplicate Db at 19-NOV-10

RMAN> exit



9.

[oracle@rhel clntomig]$ export ORACLE_SID=clntomig
[oracle@rhel clntomig]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 19 12:25:20 2010

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


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$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/clntomig/system01.dbf
/u01/app/oracle/oradata/clntomig/undo01.dbf
/u01/app/oracle/oradata/clntomig/sysaux01.dbf
/u01/app/oracle/oradata/clntomig/users01.dbf
/u01/app/oracle/oradata/clntomig/test01.dbf

--create a new temporary tablespace and drop the old one.

SQL> create temporary tablespace temp01 tempfile
'/u01/app/oracle/oradata/clntomig/temp02.dbf size 100m;

SQL> alter database default temporary tablespace temp01;

SQL> drop tablespace temp including contents and datafiles;

Another Method  (RMAN Cloning in Different/Same Server)
============

 1) Take the backup of the databse sundb (backup taken in /optware/backup location).
2) In the same server restore the database using the backup with different name (sundup).

Readiness for sundup.

1) Create password file

cd $ORACLE_HOME/dbs
orapwd file=orapwsundup password=superstar

2) Create pfile using the pfile of sundb.


Pfile of sundb

sundb.__db_cache_size=322961408
sundb.__java_pool_size=4194304
sundb.__large_pool_size=4194304
sundb.__oracle_base='/optware/oracle'#ORACLE_BASE set from environment
sundb.__pga_aggregate_target=163577856
sundb.__sga_target=486539264
sundb.__shared_io_pool_size=0
sundb.__shared_pool_size=146800640
sundb.__streams_pool_size=0
*.audit_file_dest='/optware/oracle/admin/sundb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/optware/oracle/oradata/sundb/control01.ctl',
'/optware/oracle/fast_recovery_area/sundb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='sundb'
*.db_recovery_file_dest='/optware/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/optware/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sundbXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=161480704
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=486539264
*.undo_tablespace='UNDO


Rename the sundb with sundup.

Blelow is the modified pfile for sundup.

sundup.__db_cache_size=322961408
sundup.__java_pool_size=4194304
sundup.__large_pool_size=4194304
sundup.__oracle_base='/optware/oracle'#ORACLE_BASE set from environment
sundup.__pga_aggregate_target=163577856
sundup.__sga_target=486539264
sundup.__shared_io_pool_size=0
sundup.__shared_pool_size=146800640
sundup.__streams_pool_size=0
*.audit_file_dest='/optware/oracle/admin/sundup/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/optware/oracle/oradata/sundup/control01.ctl','/optware/oracle/fast_recovery_area/sundup/control02.ctl'
*.db_file_name_convert='/optware/oracle/oradata/sundb','/optware/oracle/oradata/sundup'
*.log_file_name_convert='/optware/oracle/oradata/sundb','/optware/oracle/oradata/sundup'
*.db_block_size=8192
*.db_domain=''
*.db_name='sundup'
*.db_recovery_file_dest='/optware/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/optware/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sundupXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=161480704
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=486539264
*.undo_tablespace='UNDOTBS1'


3) Create the directories

mkdir -p /optware/oracle/admin/sundup/adump
mkdir -p /optware/oracle/oradata/sundup
mkdir -p /optware/oracle/fast_recovery_area/sundup


4) Create the Static listener for sundup and start the listener.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
         (ORACLE_HOME = /optware/oracle/product/11.2.0.3)
      (SID_NAME = sundup)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = solarisrac1 )(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /optware/oracle


-bash-3.2$ lsnrctl start listener

LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 07-DEC-2014 10:41:42

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /optware/oracle/product/11.2.0.3/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
System parameter file is /optware/oracle/product/11.2.0.3/network/admin/listener.ora
Log messages written to /optware/oracle/diag/tnslsnr/solarisrac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solarisrac1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=solarisrac1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date                07-DEC-2014 10:41:43
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /optware/oracle/product/11.2.0.3/network/admin/listener.ora
Listener Log File         /optware/oracle/diag/tnslsnr/solarisrac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solarisrac1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "sundup" has 1 instance(s).
  Instance "sundup", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


5) Create the spfile from pfile and startup the instance in nomount state.

export ORACLE_SID=sundup

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 7 11:19:31 2014

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

Connected to an idle instance.

SQL> create spfile from pfile='/optware/oracle/product/11.2.0.3/dbs/initsundup.ora';

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  484356096 bytes
Fixed Size                  2227216 bytes
Variable Size             155190256 bytes
Database Buffers          322961408 bytes
Redo Buffers                3977216 bytes
SQL> exit

7) Connect to rman with auxiliary target and execute the below command.

-bash-3.2$ rman auxiliary /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Dec 7 11:20:25 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: SUNDUP (not mounted)

RMAN> duplicate database to sundup backup location '/optware/backup';

Starting Duplicate Db at 07-DEC-14

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''SUNDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''SUNDUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/optware/backup/full_backup_20141207_05ppie87_1_1';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''SUNDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''SUNDUP'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     484356096 bytes

Fixed Size                     2227216 bytes
Variable Size                155190256 bytes
Database Buffers             322961408 bytes
Redo Buffers                   3977216 bytes

Starting restore at 07-DEC-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/optware/oracle/oradata/sundup/control01.ctl
output file name=/optware/oracle/fast_recovery_area/sundup/control02.ctl
Finished restore at 07-DEC-14

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:
{
   set until scn  975621;
   set newname for datafile  1 to
 "/optware/oracle/oradata/sundup/system01.dbf";
   set newname for datafile  2 to
 "/optware/oracle/oradata/sundup/sysaux01.dbf";
   set newname for datafile  3 to
 "/optware/oracle/oradata/sundup/undotbs01.dbf";
   set newname for datafile  4 to
 "/optware/oracle/oradata/sundup/users01.dbf";
   restore
   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 07-DEC-14
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /optware/oracle/oradata/sundup/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /optware/oracle/oradata/sundup/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /optware/backup/full_backup_20141207_03ppie4t_1_1
channel ORA_AUX_DISK_1: piece handle=/optware/backup/full_backup_20141207_03ppie4t_1_1 tag=TAG20141207T103156
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /optware/oracle/oradata/sundup/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /optware/oracle/oradata/sundup/undotbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /optware/backup/full_backup_20141207_04ppie4t_1_1
channel ORA_AUX_DISK_1: piece handle=/optware/backup/full_backup_20141207_04ppie4t_1_1 tag=TAG20141207T103156
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 07-DEC-14

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

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=865682624 file name=/optware/oracle/oradata/sundup/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=865682624 file name=/optware/oracle/oradata/sundup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=865682624 file name=/optware/oracle/oradata/sundup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=865682624 file name=/optware/oracle/oradata/sundup/users01.dbf

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

executing command: SET until clause

Starting recover at 07-DEC-14
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_AUX_DISK_1: reading from backup piece /optware/backup/full_backup_20141207_07ppie8o_1_1
channel ORA_AUX_DISK_1: piece handle=/optware/backup/full_backup_20141207_07ppie8o_1_1 tag=TAG20141207T103400
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/optware/oracle/fast_recovery_area/SUNDUP/archivelog/2014_12_07/o1_mf_1_6_b87l22xp_.arc thread=1 sequence=6
channel clone_default: deleting archived log(s)
archived log file name=/optware/oracle/fast_recovery_area/SUNDUP/archivelog/2014_12_07/o1_mf_1_6_b87l22xp_.arc RECID=1 STAMP=865682626
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-DEC-14
Oracle instance started

Total System Global Area     484356096 bytes

Fixed Size                     2227216 bytes
Variable Size                155190256 bytes
Database Buffers             322961408 bytes
Redo Buffers                   3977216 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''SUNDUP'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''SUNDUP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     484356096 bytes

Fixed Size                     2227216 bytes
Variable Size                155190256 bytes
Database Buffers             322961408 bytes
Redo Buffers                   3977216 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SUNDUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/optware/oracle/oradata/sundup/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/optware/oracle/oradata/sundup/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/optware/oracle/oradata/sundup/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/optware/oracle/oradata/sundup/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/optware/oracle/oradata/sundup/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/optware/oracle/oradata/sundup/sysaux01.dbf",
 "/optware/oracle/oradata/sundup/undotbs01.dbf",
 "/optware/oracle/oradata/sundup/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /optware/oracle/oradata/sundup/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/optware/oracle/oradata/sundup/sysaux01.dbf RECID=1 STAMP=865682653
cataloged datafile copy
datafile copy file name=/optware/oracle/oradata/sundup/undotbs01.dbf RECID=2 STAMP=865682654
cataloged datafile copy
datafile copy file name=/optware/oracle/oradata/sundup/users01.dbf RECID=3 STAMP=865682654

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=865682653 file name=/optware/oracle/oradata/sundup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=865682654 file name=/optware/oracle/oradata/sundup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=865682654 file name=/optware/oracle/oradata/sundup/users01.dbf

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

database opened
Finished Duplicate Db at 07-DEC-14

RMAN> exit


Recovery Manager complete.
-bash-3.2$
-bash-3.2$
-bash-3.2$
-bash-3.2$
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 7 11:25:30 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from V$database;

NAME
---------
SUNDUP

SQL>
SQL> exit


SQL> alter system reset db_file_name_convert scope=spfile;

System altered.

SQL> alter system reset log_file_name_convert scope=spfile;

System altered.

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


SQL> startup
ORACLE instance started.

Total System Global Area  484356096 bytes
Fixed Size                  2227216 bytes
Variable Size             155190256 bytes
Database Buffers          322961408 bytes
Redo Buffers                3977216 bytes
Database mounted.
Database opened.

SQL> select name from V$database;

NAME
---------
SUNDUP

SQL> select name from V$controlfile;

NAME
--------------------------------------------------------------------------------
/optware/oracle/oradata/sundup/control01.ctl
/optware/oracle/fast_recovery_area/sundup/control02.ctl

SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
/optware/oracle/oradata/sundup/system01.dbf
/optware/oracle/oradata/sundup/sysaux01.dbf
/optware/oracle/oradata/sundup/undotbs01.dbf
/optware/oracle/oradata/sundup/users01.dbf

SQL> select name from V$tempfile;

NAME
--------------------------------------------------------------------------------
/optware/oracle/oradata/sundup/temp01.dbf

SQL> select member from V$logfile;

MEMBER
--------------------------------------------------------------------------------
/optware/oracle/oradata/sundup/redo03.log
/optware/oracle/oradata/sundup/redo02.log
/optware/oracle/oradata/sundup/redo01.log




RMAN Cloning in Different Server:-


For Rman cloning in different server , the same methods as mentioned
above has to be followed, and the below things to be taken care.

1) Ensure to update the tnsnames.ora and the listener.ora files with correct details.
i.e. the destination server database details has to be updated in production
tnsnames.ora file and SID description has to be updated in the listener.ora
file on the destination server.

2) Copy the rman backup pieces from the production server to the destination
server and paste them in the same path as it exists in the production server.
(If the backup pieces are not copied to the destination server then while executing the duplicate command in rman will get a error as no backup found for 10g, but for 11g no need to copy the backup to target server).

3) Create the duplicate database in a new host. If the same directory structure is
available, then you can use the NOFILENAMECHECK option and reuse the target
datafile filenames for the duplicate datafiles.

-----------------------------------------------
Note

1) Create the duplicate database by using the SET UNTIL command or UNTIL clause
of the DUPLICATE command to recover it to a past time. By default, the
DUPLICATE command creates the database using the most recent backups of the
target database and then performs recovery to the most recent consistent point
contained in the incremental and archived redo log backups.

2)Use the duplicate database without a recovery catalog.

3)Register the duplicate database in the same recovery catalog as the target database. This option is possible because the duplicate database receives a new database identifier during duplication. If you copy the target database with operating system utilities, then the database identifier of the copied database remains the same so you cannot register it in the same recovery catalog (unless you change its DBID with the DBNEWID utility, described in Oracle Database Utilities ).

====================================================================================
Restoring the database backup to another server with differnet file System.
===========================================================================


High level steps/methods
------------------------


Source - ASM File system
Target - Normal file system.


1) Take the latest level 0 backup with additional archive backup and the controlfile and spfile backup from the source server.

2) Copy the backup pieces to target server.

3) Now startup the instance in the target server to restore the spfile.




rman target /

RMAN> statup nomount;


-- It will start a dummy instance.

RMAN> restore pfile from '/backup/prod/spfilebkp';


RMAN> shutdown abort;

-- Now the pfile has been restored, edit the parameter pfile as per the target server requirements and start then
start the instance again to restore controlfile.


4) Resotre the controlfile.

rman target /

RMAN> startup nomount;


RMAN> restore contorlfile from '/backup/prod/controlfile_bkp';

-- Now the controlfile will be restored.

-- Mount the controlfile.

RMAN> alter database mount;


-- Catalog all the backup pieces.

RMAN> allocate channel for maintenance device type disk;
RMAN> crosscheck backup;
RMAN> delete expired backup;


RMAN> catalog start with '/backup/prod';


RMAN> run
{
allocate channel a1 device type disk;
allocate channel a2 device type disk;
allocate channel a3 device type disk;
set newname for datafile 1 to '/u01/prod/manzy/oradata/system01.dbf';
set newname for datafile 2 to '/u01/prod/manzy/oradata/sysaux01.dbf';
---
---
---
restore database;
switch all datafile;
}



-- The above command will allocates 3 channels and it will set the new name for the datafiles to the name
which we have defined...


eg.

In the source server the filesystem is asm and the datafile 1 is like '+MANYDG/datafile/system01.dbf'.

Here while restoring we have set the new name to point to the normal file system... like wise we need to set the
new name for all the datafiles.


-- restore dastabase .. will restore all the datafiles from the backup to the new location we have mentioned.
-- switch all datafile .. it will update the controlfile about the new name for all the datafile.


For incomplete recovery
-----------------------

-- Check up to which log sequence / time / scn you want to resotre accordingly.



RMAN> run
{
set until sequence 7000;
allocate channel a1 device type disk;
recover database;
}





-- Now connect the db and create the new logfiles to point to the nfs file system.

Note : -- If we open the database without creating it, then we will get the below error.


ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '+MANZYDG/onlinelog/redo01.log'
ORA-17503: ksfdopn:2 Failed to open file +MANZYDG/onlinelog/redo4b.log
ORA-15001: diskgroup "MANZYDG" does not exist or is not mounted

sqlplus / as sysdba


sql> select * from V$log;



-- Delete the log groups which are showing as inactive status.

SQL> alter database drop logfile group 1;

sql> alter database add logfile group 1 ('/u01/prod/manzy/redo01.log') size 512m;


-- You cannot drop the current/active logfile group.



-- If you have any currnet/active logfile groups which still points to asm then... recover the db with some more archives...


eg.. if the currnet logfile sequence is 6995 .. we have already recoverd the db upto sequence 7000, hence this log is not required
so take the backup of the controlfile trace and recreate it with RESETLOG option.



CREATE CONTROLFILE REUSE DATABASE "MANPRD" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/u01/prod/manzy/oradata/redo01.log'  size 512m,
  GROUP 2 '/u01/prod/manzy/oradata/redo02.log'  SIZE 512M,
  GROUP 3 '/u01/prod/manzy/oradata/redo03.log'  SIZE 512M
DATAFILE
  '/u01/prod/manzy/oradata/system.260.703794419',
  '/u01/prod/manzy/oradata/undotbs1.261.703794423',
  '/u01/prod/manzy/oradata/sysaux.262.703794425',
  '/u01/prod/manzy/oradata/users.264.703794427',
   '/u01/prod/manzy/oradata/xcom_data28.dbf'
CHARACTER SET WE8ISO8859P1
/


alter database open resetlogs;


create the tempfile...


alter tablespace temp add tempfile '/u01/prod/manzy/oradata/temp01.dbf' size 1g;


--Now the db is ready for use.



HOT backup cloning of RAC DB on Same server
===================================



Cloning RAC database on Same Server:-
--------------------------------------

I) Using hot backup method.


Source Database - sgculdb
Target database - sgdupdb


[oracle@rhel11grac1 ~]$ srvctl status database -d sgculdb
Instance sgculdb1 is running on node rhel11grac1
Instance sgculdb2 is running on node rhel11grac2


Size of source database datafiles.

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files;

SUM(BYTES)/1024/1024/1024
-------------------------
               1.33300781


SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
+DATA_DG/sgculdb/datafile/system.256.908189315
+DATA_DG/sgculdb/datafile/sysaux.257.908189317
+DATA_DG/sgculdb/datafile/undotbs1.258.908189317
+DATA_DG/sgculdb/datafile/users.259.908189317
+DATA_DG/sgculdb/datafile/undotbs2.264.908189793



a) Create ASM diskgroup for duplicate db (Target db)



SQL> CREATE diskgroup SGDUP_DATA external redundancy disk '/dev/mapper/fra_d2_disk1p1'
      ATTRIBUTE
     'au_size'='4M',
     'compatible.asm' = '11.2',
     'compatible.rdbms' = '11.2',
     'compatible.advm' = '11.2'
     /

Diskgroup created.


Create datafile directories.


SQL> alter diskgroup SGDUP_DATA add directory '+SGDUP_DATA/sgdupdb';

Diskgroup altered.

SQL> alter diskgroup SGDUP_DATA add directory '+SGDUP_DATA/sgdupdb/datafile';

Diskgroup altered.

SQL> alter diskgroup SGDUP_DATA add directory '+SGDUP_DATA/sgdupdb/onlinelog';

Diskgroup altered.

SQL> alter diskgroup SGDUP_DATA add directory '+SGDUP_DATA/sgdupdb/controlfile';

Diskgroup altered.


b) Put the source db in backup mode and copy the datafiles to the target diskgroup.



SQL> alter database begin backup;

Database altered.


User the below SQL to generate the asmcmd copy command to copy datafiles, note that we have generating a random
number and adding to the target datafile side to avoid overwriting of datafiles.


SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
+DATA_DG/sgculdb/datafile/system.256.908189315
+DATA_DG/sgculdb/datafile/sysaux.257.908189317
+DATA_DG/sgculdb/datafile/undotbs1.258.908189317
+DATA_DG/sgculdb/datafile/users.259.908189317
+DATA_DG/sgculdb/datafile/undotbs2.264.908189793

SQL> select 'asmcmd -p cp ''' || name || '''' || ' ''+SGDUP_DATA/sgdupdb/datafile/' || substr(name,instr(name,'/',1,3)+1,instr(name,'.',1,1)-instr(name,'/',1,3)-1) ||
     round(dbms_random.value(1,5000)) || ''' >> $log' from v$datafile;

'ASMCMD-PCP'''||NAME||''''||'''+SGDUP_DATA/SGDUPDB/DATAFILE/'||SUBSTR(NAME,INSTR(NAME,'/',1,3)+1,INSTR(NAME,'.',1,1)-INSTR(NAME,'/',1,3)-1)||ROUND(DBMS_RANDOM.VALUE(1,5000))||'''>>$LOG'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
asmcmd -p cp '+DATA_DG/sgculdb/datafile/system.256.908189315' '+SGDUP_DATA/sgdupdb/datafile/system3611' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/sysaux.257.908189317' '+SGDUP_DATA/sgdupdb/datafile/sysaux3630' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/undotbs1.258.908189317' '+SGDUP_DATA/sgdupdb/datafile/undotbs1431' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/users.259.908189317' '+SGDUP_DATA/sgdupdb/datafile/users4086' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/undotbs2.264.908189793' '+SGDUP_DATA/sgdupdb/datafile/undotbs21832' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/manzoor.268.909137345' '+SGDUP_DATA/sgdupdb/datafile/manzoor1279' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/manzoor.269.909137369' '+SGDUP_DATA/sgdupdb/datafile/manzoor4268' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/manzoor02.dbf' '+SGDUP_DATA/sgdupdb/datafile/manzoor022546' >> $log




c) Create a script to copy the datafiles as below

$ vi asm_file_copy.sh



#!/bin/sh

export ORCLE_SID=+ASM1
export ORACLE_HOME=/u01/grid/product/11.2.0.4
export PATH=$ORACLE_HOME/bin:$PATH
log=/home/grid/copy_log.log
echo "Starting Copy" `date` > $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/system.256.908189315' '+SGDUP_DATA/sgdupdb/datafile/system3611' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/sysaux.257.908189317' '+SGDUP_DATA/sgdupdb/datafile/sysaux3630' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/undotbs1.258.908189317' '+SGDUP_DATA/sgdupdb/datafile/undotbs1431' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/users.259.908189317' '+SGDUP_DATA/sgdupdb/datafile/users4086' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/undotbs2.264.908189793' '+SGDUP_DATA/sgdupdb/datafile/undotbs21832' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/manzoor.268.909137345' '+SGDUP_DATA/sgdupdb/datafile/manzoor1279' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/manzoor.269.909137369' '+SGDUP_DATA/sgdupdb/datafile/manzoor4268' >> $log
asmcmd -p cp '+DATA_DG/sgculdb/datafile/manzoor02.dbf' '+SGDUP_DATA/sgdupdb/datafile/manzoor022546' >> $log
echo "Copy completed" `date` >> $log


d) Execte the scitp to start the copy.


$ nohup ./asm_file_copy.sh &


Once completed verify the logfile.

$cat copy_log.log

Starting Copy Thu Apr 14 10:28:39 SGT 2016
copying +DATA_DG/sgculdb/datafile/system.256.908189315 -> +SGDUP_DATA/sgdupdb/datafile/system3611
copying +DATA_DG/sgculdb/datafile/sysaux.257.908189317 -> +SGDUP_DATA/sgdupdb/datafile/sysaux3630
copying +DATA_DG/sgculdb/datafile/undotbs1.258.908189317 -> +SGDUP_DATA/sgdupdb/datafile/undotbs1431
copying +DATA_DG/sgculdb/datafile/users.259.908189317 -> +SGDUP_DATA/sgdupdb/datafile/users4086
copying +DATA_DG/sgculdb/datafile/undotbs2.264.908189793 -> +SGDUP_DATA/sgdupdb/datafile/undotbs21832
copying +DATA_DG/sgculdb/datafile/manzoor.268.909137345 -> +SGDUP_DATA/sgdupdb/datafile/manzoor1279
copying +DATA_DG/sgculdb/datafile/manzoor.269.909137369 -> +SGDUP_DATA/sgdupdb/datafile/manzoor4268
copying +DATA_DG/sgculdb/datafile/manzoor02.dbf -> +SGDUP_DATA/sgdupdb/datafile/manzoor022546
Copy completed Thu Apr 14 10:30:26 SGT 2016


All the files are copied without error.


e) End the backup mode in the database.


SQL> alter database end backup;

Database altered.


<< Make sure to retain all the archive logs which are generated during begin backup and end backup >>



f) Create a controlfile trace backup and pfile backup from source database.


SQL> alter database backup controlfile to trace as '/home/oracle/controlfile_trace_for_dup.txt' resetlogs;

Database altered.

SQL> create pfile='/home/oracle/pfile_for_dup.ora' from spfile;

File created.


Now modify the pfile accordginly which will be used for the target duplicate db. Below is the modified pfile
as per the target db. Filename is ( initsgdupdb1.ora)


*.audit_file_dest='/u01/app/oracle/admin/sgdupdb/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+SGDUP_DATA/sgdupdb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+SGDUP_DATA'
*.db_domain=''
*.db_name='sgdupdb'
*.db_recovery_file_dest='+SGDUP_DATA'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sgdupdbXDB)'
sgdupdb1.instance_number=1
sgdupdb2.instance_number=2
*.log_archive_dest_1='LOCATION=+DATA_DG/arch'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_listener='home-scan.manzoor.com:1521'
*.remote_login_passwordfile='exclusive'
sgdupdb2.thread=2
sgdupdb1.thread=1
sgdupdb1.undo_tablespace='UNDOTBS1'
sgdupdb2.undo_tablespace='UNDOTBS2'


g) Start the instance in nomount state.

$ export ORACLE_SID=sgdupdb1

[oracle@rhel11grac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 14 11:05:51 2016

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes




Use the below command to get the target datafiles files which we have copied from source. Copy the output of the command
and paste to the controlfile datafile section. Make sure to remove the comma at the end of the last file.


[grid@rhel11grac1 ~]$ asmcmd -p ls -l SGDUP_DATA/sgdupdb/datafile | awk -F= '{print $2}' | awk '{print $2}' > file_names.log
[grid@rhel11grac1 ~]$ cat file_names.log

+SGDUP_DATA/ASM/DATAFILE/manzoor022546.263.909138625
+SGDUP_DATA/ASM/DATAFILE/manzoor1279.261.909138621
+SGDUP_DATA/ASM/DATAFILE/manzoor4268.262.909138623
+SGDUP_DATA/ASM/DATAFILE/sysaux3630.257.909138565
+SGDUP_DATA/ASM/DATAFILE/system3611.256.909138519
+SGDUP_DATA/ASM/DATAFILE/undotbs1431.258.909138609
+SGDUP_DATA/ASM/DATAFILE/undotbs21832.260.909138617
+SGDUP_DATA/ASM/DATAFILE/users4086.259.909138615



[grid@rhel11grac1 ~]$ for i in `cat file_names.log`
 do
 echo "'$i'," >> modified_files
 done
[grid@rhel11grac1 ~]$ cat modified_files
'+SGDUP_DATA/ASM/DATAFILE/manzoor022546.263.909138625',
'+SGDUP_DATA/ASM/DATAFILE/manzoor1279.261.909138621',
'+SGDUP_DATA/ASM/DATAFILE/manzoor4268.262.909138623',
'+SGDUP_DATA/ASM/DATAFILE/sysaux3630.257.909138565',
'+SGDUP_DATA/ASM/DATAFILE/system3611.256.909138519',
'+SGDUP_DATA/ASM/DATAFILE/undotbs1431.258.909138609',
'+SGDUP_DATA/ASM/DATAFILE/undotbs21832.260.909138617',
'+SGDUP_DATA/ASM/DATAFILE/users4086.259.909138615',






h) Now we have to create the controlfile for the target database using the trace file
which we have taken from the source db. Below is the modified controlfile file
Changes made are on the first line i.e. create controlfile statement, redo log and datafile locations.

CREATE CONTROLFILE SET DATABASE "SGDUPDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+SGDUP_DATA' size 50m ,
  GROUP 2 '+SGDUP_DATA' size 50m
-- STANDBY LOGFILE
DATAFILE
'+SGDUP_DATA/ASM/DATAFILE/manzoor022546.263.909138625',
'+SGDUP_DATA/ASM/DATAFILE/manzoor1279.261.909138621',
'+SGDUP_DATA/ASM/DATAFILE/manzoor4268.262.909138623',
'+SGDUP_DATA/ASM/DATAFILE/sysaux3630.257.909138565',
'+SGDUP_DATA/ASM/DATAFILE/system3611.256.909138519',
'+SGDUP_DATA/ASM/DATAFILE/undotbs1431.258.909138609',
'+SGDUP_DATA/ASM/DATAFILE/undotbs21832.260.909138617',
'+SGDUP_DATA/ASM/DATAFILE/users4086.259.909138615'
CHARACTER SET AL32UTF8
;


i) Now execute the controlfile script.

SQL> @controlfile_trace_for_dup.txt

Control file created.



<< Controlfile has been created >>

j) Now recover the database as we have used the hot backup of the database, we have provide the logfile (either archivelog or online redo log
of the source database)


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1143420 generated at 04/14/2016 10:22:26 needed for thread 1
ORA-00289: suggestion : +DATA_DG/arch/1_14_908189542.dbf
ORA-00280: change 1143420 for thread 1 is in sequence #14


Specify log: {=suggested | filename | AUTO | CANCEL}
+DATA_dG/sgculdb/onlinelog/group_2.262.908189553
Log applied.
Media recovery complete.

k) Open the database using resetlog options.


SQL> alter database open resetlogs;

Database altered.


Now create the spfile and restart the db.


SQL> create spfile='+SGDUP_DATA' from pfile;

File created.



Modify the pfile to point the location of the spfile (Get the fully qualified spfile name in asmcmd).


ASMCMD> ls -lrt +SGDUP_DATA/sgdupdb/parameterfile/
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   APR 14 11:00:00  Y    spfile.269.909142495


Update the initsgculdb1.ora as below

spfile='+SGDUP_DATA/sgdupdb/parameterfile/spfile.269.909142495'


l) Restart  the database.

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


SQL> startup ;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
Database mounted.
Database opened.


SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +SGDUP_DATA/sgdupdb/parameterf
                                                 ile/spfile.269.909142495



SQL> alter system set cluster_database=true scope=spfile;

System altered.

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

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
Database mounted.
Database opened.


M) Add logfile for thread 2 and enable it.


SQL> alter database add logfile thread 2 group 3 ('+SGDUP_DATA') size 50m;

Database altered.

SQL> alter database add logfile thread 2 group 4 ('+SGDUP_DATA') size 50m;

Database altered.

SQL> alter database enable public thread 2;

Database altered.



Copy the init paramter file to the 2nd node


[oracle@rhel11grac1 ~]$ scp /u01/app/oracle/product/11.2.0.3/dbs/initsgdupdb1.ora rhel11grac2:/u01/app/oracle/product/11.2.0.3/dbs/initsgdupdb2.ora
initsgdupdb1.ora        

<< Make sure to create any directories as required for the node 2 eg. audit adump directory)


Startup the node 2.


[oracle@rhel11grac2 ~]$ export ORACLE_SID=sgdupdb2
[oracle@rhel11grac2 ~]$ mkdir -p /u01/app/oracle/admin/sgdupdb/adump
[oracle@rhel11grac2 ~]$ sqlplus / as sysdba

SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
Database mounted.
Database opened.


SQL> select inst_id, status from GV$instance;

   INST_ID STATUS
---------- ------------
         2 OPEN
         1 OPEN


Create tempfiles.

SQL> select name from V$tempfile;

no rows selected

SQL> alter tablespace temp add tempfile '+SGDUP_DATA' size 50m;

Tablespace altered.


N) Register the database in CRS.

[oracle@rhel11grac2 ~]$ srvctl add database -d sgdupdb -o $ORACLE_HOME -c RAC -p +SGDUP_DATA/sgdupdb/parameterfile/spfile.269.909142495
[oracle@rhel11grac2 ~]$ srvctl add instance -d sgdupdb -i sgdupdb1 -n rhel11grac1
[oracle@rhel11grac2 ~]$ srvctl add instance -d sgdupdb -i sgdupdb2 -n rhel11grac2
[oracle@rhel11grac2 ~]$ srvctl start database -d sgdupdb
[oracle@rhel11grac2 ~]$ srvctl status database -d sgdupdb
Instance sgdupdb1 is running on node rhel11grac1
Instance sgdupdb2 is running on node rhel11grac2
[oracle@rhel11grac2 ~]$ srvctl stop database -d sgdupdb
[oracle@rhel11grac2 ~]$ srvctl start database -d sgdupdb
Instance sgdupdb1 is running on node rhel11grac1
Instance sgdupdb2 is running on node rhel11grac2

-- Hot backup cloning of RAC database completed.


























Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)