RMAN duplicate RAC to single instance - how to not duplicate redo threads and temp tablspace

[neil campbell]

Hi all,

Using 11.2.0.2 and trying to work out if I can do an RMAN duplicate from 4 node RAC to single instance without
the duplicate generating the 4 threads of redo?

Also, I wondered if it was possible to have the duplicate NOT create the TEMP tablespace as part of the duplicate?

I am running the duplicate as part of an automated script, and it is a simple statement as follows

duplicate database PROD to TEST pfile='/u01/app/oracle/test/pfile.test' backup location='/orabackup'

This works very well, but the reason behind the requirement to not create additional threads and TEMP tablespace is the limited amount of space
in the TEST environment at the moment. Removing the threads and TEMP tablespace would free up 48 Gb as the
test environment doesn't run the batch jobs that use all that TEMP space.

Open to suggestions...

Thanks
Neil

--
http://www.freelists.org/webpage/oracle-l

[Norman Dunbar]

Morning Neil,

Post by neil campbell
Using 11.2.0.2 and trying to work out if I can do an RMAN duplicate from 4 node RAC to single instance without
the duplicate generating the 4 threads of redo?

Ok, I'm not an RMAN expert by any means, nor do I play one on TV, but,
I'm doing a bit of RMAN at the moment - duplicating terabyte databases
over a network to a different server - and while not RAC, I'm pretty
sure that RMAN only takes the redo that it needs to restore the backup
to surrent. If you had a block updated by all 4 nodes, then you would
need all 4 redos to bring said block up to date.

Post by neil campbell
Also, I wondered if it was possible to have the duplicate NOT create the TEMP tablespace as part of the duplicate?

No. As far as I know, TEMP is recreated at the auxiliary database end as
opposed to being copied over. You could, maybe, try the skip tablespace
option to the duplicate command? The docs seem to indicate that any
tablespace can be skipped, except SYSTEM.

Post by neil campbell
I am running the duplicate as part of an automated script, and it is a simple statement as follows
duplicate database PROD to TEST pfile='/u01/app/oracle/test/pfile.test' backup location='/orabackup'

Have you tried the duplication from active database so you don't need
the dump? That's what I'm doing.

Startup nomount the aux database with a pfile containing
db_name='whatever' only, then creating a password file and a listener.

On the target database server I've got a tnsnames.ora entry pointing at
the listener and I can connect sys/***@auxiliary as sysdba with no problems.

Then rman target / on the target database server and:

connect sys/***@auxiliary

duplicate target database to auxiliary
nofilenamecheck
from active database
spfile
parameter_value_convert='/srv/SRC/oradata/SRC_SID','/srv/DST/oradata/DST_SID','/srv/SRC/flashback_area/SRC_SID','/srv/DST/flashback_area/DST_SID','SRC','DST','SRC_SID','DST_SID'
set
log_file_name_convert='/srv/SRC/oradata/SRC_SID','/srv/DST/oradata/DST_SID','/srv/SRC/flashback_area/SRC_SID','/srv/DST/flashback_area/DST_SID'
db_file_name_convert='/srv/SRC/oradata/SRC_SID','/srv/DST/oradata/DST_SID'
;

I had to put quite a lot into the parameter_value_convert because some
paths were not being converted until I added in the final 4 entries.
Strange - other clones worked fine.

Apologies if granny already knows how to suck eggs!

Post by neil campbell
This works very well, but the reason behind the requirement to not create additional threads and TEMP tablespace is the limited amount of space
in the TEST environment at the moment. Removing the threads and TEMP tablespace would free up 48 Gb as the
test environment doesn't run the batch jobs that use all that TEMP space.

Cheers,
Norm.
--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l

[Taylor, Chris David]

Neil,

Not sure about the threads of redo BUT regarding:

" Also, I wondered if it was possible to have the duplicate NOT create the TEMP tablespace as part of the duplicate?"

1. Set your UNTIL TIME to a *future* date and the RMAN duplicate will "fail" at the end
2. At this point your auxiliary database is an exact copy of your target database (DBID and all)
3. Now, if you don't use an RMAN catalog, the DBID is not a problem but if you *do* use an RMAN catalog and both the TARGET & AUXILIARY database are going to be stored in the catalog, then you *must* use NID to change the DBID of the AUXILIARY db - the one you created.
To do this: 1. set (or export) ORACLE_SID=SID_NAME 2. Mount database 3. nid target=/ 4. Follow prompts and open database resetlogs;
4. At this point you have 0 temp tablespace data files
5. The TEMP tablespace is still part of the database however so you can a.) add datafiles to the tablespace, or b.) drop and recreate the tablespace as you want
6. To add datafiles to the temp tablespace after this approach (but I think the file has to exist - not sure if a touch/fsutil command would work here though):
ALTER TABLESPACE TEMP ADD TEMPFILE '/filesystem/filename.dbf'
SIZE 1000M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 8000M ;
7. Probably you would want to drop/recreate the temp tablespace with the size you want.

HTH


Chris Taylor

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees.


-----Original Message-----
From: oracle-l-bounce-***@public.gmane.org [mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of neil campbell
Sent: Wednesday, March 28, 2012 12:01 AM
To: Oracle List
Subject: RMAN duplicate RAC to single instance - how to not duplicate redo threads and temp tablspace

Hi all,

Using 11.2.0.2 and trying to work out if I can do an RMAN duplicate from 4 node RAC to single instance without the duplicate generating the 4 threads of redo?

Also, I wondered if it was possible to have the duplicate NOT create the TEMP tablespace as part of the duplicate?

I am running the duplicate as part of an automated script, and it is a simple statement as follows

duplicate database PROD to TEST pfile='/u01/app/oracle/test/pfile.test' backup location='/orabackup'

This works very well, but the reason behind the requirement to not create additional threads and TEMP tablespace is the limited amount of space in the TEST environment at the moment. Removing the threads and TEMP tablespace would free up 48 Gb as the test environment doesn't run the batch jobs that use all that TEMP space.

Open to suggestions...

Thanks
Neil

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

[Taylor, Chris David]

(reformatted)
Neil,

Not sure about the threads of redo BUT regarding:

" Also, I wondered if it was possible to have the duplicate NOT create the TEMP tablespace as part of the duplicate?"

1. Set your UNTIL TIME to a *future* date and the RMAN duplicate will "fail" at the end

2. At this point your auxiliary database is an exact copy of your target database (DBID and all)

3. Now, if you don't use an RMAN catalog, the DBID is not a problem but if you *do* use an RMAN catalog and both the TARGET & AUXILIARY database are going to be stored in the catalog, then you *must* use NID to change the DBID of the AUXILIARY db - the one you created.
To do this: 1. set (or export) ORACLE_SID=SID_NAME 2. Mount database 3. nid target=/ 4. Follow prompts and open database resetlogs;

4. At this point you have 0 temp tablespace data files

5. The TEMP tablespace is still part of the database however so you can a.) add datafiles to the tablespace, or b.) drop and recreate the tablespace as you want

6. To add datafiles to the temp tablespace after this approach (but I think the file has to exist - not sure if a touch/fsutil command would work here though):
ALTER TABLESPACE TEMP ADD TEMPFILE '/filesystem/filename.dbf'
SIZE 1000M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 8000M ;

7. Probably you would want to drop/recreate the temp tablespace with the size you want.

HTH


Chris Taylor

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees.

-----Original Message-----
From: oracle-l-bounce-***@public.gmane.org [mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of Taylor, Chris David
Sent: Wednesday, March 28, 2012 6:57 AM
To: 'campbell.neil-***@public.gmane.org'; 'Oracle List'
Subject: RE: RMAN duplicate RAC to single instance - how to not duplicate redo threads and temp tablspace



--
http://www.freelists.org/webpage/oracle-l

[Marcin Przepiorowski]

On Wed, Mar 28, 2012 at 6:00 AM, neil campbell

Post by neil campbell
Hi all,
Using 11.2.0.2 and trying to work out if I can do an RMAN duplicate from 4 node RAC to single instance without
the duplicate generating the 4 threads of redo?

Hi

One more think about redo logs - you can specify new redo log groups
during duplication process

duplicate target database .......
logfile
'/new/path/file1' size X m,
'/new/path/file2' size X m;

regards,
Marcin

[Don Granaman]

I do this all the time. Obviously, you cannot "skip" the redo or undo for any instance and still perform recovery, but you can duplicate the database and when it is done do (for a two-instance RAC database clone to an exclusive database)):

alter database disable thread 2;
alter database drop logfile group 7;
alter database drop logfile group 8;
...
drop tablespace UNDOTBS2 including contents and datafiles;
...
(Modify as appropriate for your system, redo logs and undo tablespaces.)

For the temp tablespace, I haven't tried the other suggestions, but I just do something akin to (using Oracle-managed files):
create temporary tablespace tmptmp tempfile size 100M;
alter database default temporary tablespace TMPTMP;
drop tablespace TEMP including contents and datafiles;
create temporary tablespace TEMP tempfile size 1024M;
alter database default temporary tablespace TEMP;
drop tablespace TMPTMP including contents and datafiles;

Of course there are other options...

Don Granaman | Phone: [phone] | Cell: 402-960-6955 | Fax: [phone] | Solutionary | Relevant . Intelligent . Security

-----Original Message-----
From: oracle-l-bounce-***@public.gmane.org [mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of neil campbell
Sent: Wednesday, March 28, 2012 12:01 AM
To: Oracle List
Subject: RMAN duplicate RAC to single instance - how to not duplicate redo threads and temp tablspace

Hi all,

Using 11.2.0.2 and trying to work out if I can do an RMAN duplicate from 4 node RAC to single instance without
the duplicate generating the 4 threads of redo?

Also, I wondered if it was possible to have the duplicate NOT create the TEMP tablespace as part of the duplicate?

I am running the duplicate as part of an automated script, and it is a simple statement as follows

duplicate database PROD to TEST pfile='/u01/app/oracle/test/pfile.test' backup location='/orabackup'

This works very well, but the reason behind the requirement to not create additional threads and TEMP tablespace is the limited amount of space
in the TEST environment at the moment. Removing the threads and TEMP tablespace would free up 48 Gb as the
test environment doesn't run the batch jobs that use all that TEMP space.

Open to suggestions...

Thanks
Neil

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

[neil campbell]

Thanks Don,

I know I can do this post duplicate (as you have indicated) but I really want to do it as part of the duplicate so that the redo logs dont get created if they dont have to be,
and the TEMP tablespace doesn't get created if it doesn't have to.

I have a good suggestion from Chris Taylor on how to do this with the temp tablespace, and I am trying Marcins idea with the syntax for the redo logs,
but I just keep getting the following error

RMAN-06136: ORACLE error from auxiliary database: ORA-01518: CREATE DATABASE must specify more than one log file

I am using the syntax

duplicate database PROD to TEST pfile='pfile.TEST' backup location='/backup/PROD' LOGFILE INSTANCE 'TEST1' GROUP 1 ('+DATA_TEST','+DATA_TEST') SIZE 1000M, INSTANCE 'TEST2' GROUP 2 ('+DATA_TEST','+DATA_TEST') SIZE 1000M;

Thanks for your help guys.

Neil

Date: Thu, 29 Mar 2012 13:54:12 -0500
Subject: RE: RMAN duplicate RAC to single instance - how to not duplicate redo threads and temp tablespace
alter database disable thread 2;
alter database drop logfile group 7;
alter database drop logfile group 8;
...
drop tablespace UNDOTBS2 including contents and datafiles;
...
(Modify as appropriate for your system, redo logs and undo tablespaces.)
create temporary tablespace tmptmp tempfile size 100M;
alter database default temporary tablespace TMPTMP;
drop tablespace TEMP including contents and datafiles;
create temporary tablespace TEMP tempfile size 1024M;
alter database default temporary tablespace TEMP;
drop tablespace TMPTMP including contents and datafiles;
Of course there are other options...
Don Granaman | Phone: [phone] | Cell: 402-960-6955 | Fax: [phone] | Solutionary | Relevant . Intelligent . Security
-----Original Message-----
Sent: Wednesday, March 28, 2012 12:01 AM
To: Oracle List
Subject: RMAN duplicate RAC to single instance - how to not duplicate redo threads and temp tablspace
Hi all,
Using 11.2.0.2 and trying to work out if I can do an RMAN duplicate from 4 node RAC to single instance without
the duplicate generating the 4 threads of redo?
Also, I wondered if it was possible to have the duplicate NOT create the TEMP tablespace as part of the duplicate?
I am running the duplicate as part of an automated script, and it is a simple statement as follows
duplicate database PROD to TEST pfile='/u01/app/oracle/test/pfile.test' backup location='/orabackup'
This works very well, but the reason behind the requirement to not create additional threads and TEMP tablespace is the limited amount of space
in the TEST environment at the moment. Removing the threads and TEMP tablespace would free up 48 Gb as the
test environment doesn't run the batch jobs that use all that TEMP space.
Open to suggestions...
Thanks
Neil
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

[kathryn axelrod]

Hi,

If you are duplicating it to a set time/sequence number such that all
post-backup-data would be from archivelogs and it would do an open
resetlogs, does this work:

duplicate database PROD to TEST pfile='pfile.TEST' backup
location='/backup/PROD' LOGFILE GROUP 1 ('+DATA_TEST','+DATA_TEST')
SIZE 1000M, GROUP 2 ('+DATA_TEST','+DATA_TEST') SIZE 1000M;




On Thu, Mar 29, 2012 at 5:20 PM, neil campbell

Post by neil campbell
...
I am using the syntax
duplicate database PROD to TEST pfile='pfile.TEST' backup location='/backup/PROD' LOGFILE INSTANCE 'TEST1' GROUP 1 ('+DATA_TEST','+DATA_TEST') SIZE 1000M, INSTANCE 'TEST2' GROUP 2 ('+DATA_TEST','+DATA_TEST') SIZE 1000M;

--
http://www.freelists.org/webpage/oracle-l

[Don Granaman]

This is attempting to create one redo group each for two instances. You must have at least two redo groups per instance - as Kathryn suggested.

Don Granaman | Phone: [phone] | Cell: 402-960-6955 | Fax: [phone] | Solutionary | Relevant . Intelligent . Security


-----Original Message-----
From: oracle-l-bounce-***@public.gmane.org [mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of neil campbell
Sent: Thursday, March 29, 2012 7:20 PM
To: Oracle List
Subject: RE: RMAN duplicate RAC to single instance - how to not duplicate redo threads and temp tablespace




Thanks Don,

I know I can do this post duplicate (as you have indicated) but I really want to do it as part of the duplicate so that the redo logs dont get created if they dont have to be, and the TEMP tablespace doesn't get created if it doesn't have to.

I have a good suggestion from Chris Taylor on how to do this with the temp tablespace, and I am trying Marcins idea with the syntax for the redo logs, but I just keep getting the following error

RMAN-06136: ORACLE error from auxiliary database: ORA-01518: CREATE DATABASE must specify more than one log file

I am using the syntax

duplicate database PROD to TEST pfile='pfile.TEST' backup location='/backup/PROD' LOGFILE INSTANCE 'TEST1' GROUP 1 ('+DATA_TEST','+DATA_TEST') SIZE 1000M, INSTANCE 'TEST2' GROUP 2 ('+DATA_TEST','+DATA_TEST') SIZE 1000M;

Thanks for your help guys.

Neil

Date: Thu, 29 Mar 2012 13:54:12 -0500
Subject: RE: RMAN duplicate RAC to single instance - how to not
duplicate redo threads and temp tablespace
alter database disable thread 2;
alter database drop logfile group 7;
alter database drop logfile group 8;
...
drop tablespace UNDOTBS2 including contents and datafiles; ...
(Modify as appropriate for your system, redo logs and undo
tablespaces.)
create temporary tablespace tmptmp tempfile size 100M; alter database
default temporary tablespace TMPTMP; drop tablespace TEMP including
contents and datafiles; create temporary tablespace TEMP tempfile size
1024M; alter database default temporary tablespace TEMP; drop
tablespace TMPTMP including contents and datafiles;
Of course there are other options...
402-361-3173 | Solutionary | Relevant . Intelligent . Security
-----Original Message-----
Sent: Wednesday, March 28, 2012 12:01 AM
To: Oracle List
Subject: RMAN duplicate RAC to single instance - how to not duplicate
redo threads and temp tablspace
Hi all,
Using 11.2.0.2 and trying to work out if I can do an RMAN duplicate
from 4 node RAC to single instance without the duplicate generating the 4 threads of redo?
Also, I wondered if it was possible to have the duplicate NOT create the TEMP tablespace as part of the duplicate?
I am running the duplicate as part of an automated script, and it is a
simple statement as follows
duplicate database PROD to TEST pfile='/u01/app/oracle/test/pfile.test' backup location='/orabackup'
This works very well, but the reason behind the requirement to not
create additional threads and TEMP tablespace is the limited amount of
space in the TEST environment at the moment. Removing the threads and TEMP tablespace would free up 48 Gb as the test environment doesn't run the batch jobs that use all that TEMP space.
Open to suggestions...
Thanks
Neil
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l