CLOUDARCHITECT: Create Duplicate DB using Cold, Hot RMAN backup

Create Duplicate DB using Cold, Hot RMAN backup

Clone an Oracle database using an online/hot backup

This procedure will clone a database using a online copy of the source database files. Before beginning though, there are a few things that are worth noting about online/hot backups:

When a tablespace is put into backup mode, Oracle will write entire blocks to redo rather than the usual change vectors. For this reason, do not perform a hot backup during periods of heavy database activity - it could lead to a lot of archive logs being created.
This procedure will put all tablespaces into backup mode at the same time. If the source database is quite large and you think that it might take a long time to copy, consider copying the tablespaces one at a time, or in groups.
While the backup is in progress, it will not be possible to take the tablespaces offline normally or shut down the instance.

Ok, lets get started...

1. Make a note of the current archive log change number

Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:
```
select max(first_change#) chng from v$archived_log

/
```
2. Prepare the begin/end backup scripts

The following sql will produce two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it:

3. Put the source database into backup mode

From sqlplus, run the begin backup script created in the last step:


@begin_backup

This will put all of the <span class="GRcorrect">databases</span> <span class="GRnoSuggestion GRcorrect">tablespaces</span> into backup mode.

4. Copy the files to the new location

Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.
5. Take the source database out of backup mode

Once the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2. From sqlplus:
```
@end_backup
```

6. Copy archive logs

It is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:


<span class="GRcorrect">alter</span> system archive log current;

Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in step 1.


<span class="GRcorrect">select name
from</span> v$archived_log

<span class="GRcorrect">where</span> first_change# >= &change_no
<span class="GRcorrect">order</span> by name
/

Create an archive directory <span class="GRcorrect">in</span> the clone database<span class="GRcorrect">.</<span class="GRcorrect">span>s</span> file system and copy all of the identified logs into it.

7. Produce a pfile for the new database

This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

From sqlplus:


<span class="GRcorrect">create</span> pfile='initnew database from <span class="GRcorrect">sid.</span><span class="GRnoSuggestion GRcorrect">ora</span>' from <span class="GRnoSuggestion GRcorrect">spfile</span>;
This will create a new <span class="GRcorrect">pfile</span> in the $ORACLE_HOME/<span class="GRcorrect">dbs</span> directory.

Once created, the new <span class="GRcorrect">pfile</span> will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

Ensure that the archive log destination is pointing to the directory created in step 6.

8. Create the clone controlfile

Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:


<<span class="GRcorrect">span</span> class="GRcorrect">alter</span> database backup controlfile to trace as '/home/oracle/cr_<new <span class="GRcorrect"><span class="GRcorrect">sid</span></span>>.<<span class="GRcorrect">span</span> class="GRcorrect"><span class="GRcorrect">sql</span></span>'
/

The file will require extensive editing before it can be used. Using your <span class="GRcorrect"><span class="GRcorrect">favourite</span></<span class="GRcorrect">span</span>> editor make the following alterations:

Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
Remove any lines that start with --
Remove any lines that start with a #
Remove any blank lines in the 'CREATE CONTROLFILE' section.
Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
Remove the line 'ALTER DATABASE OPEN RESETLOGS;'
Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file. Make sure that you hang onto the command, it will be used later.
Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
If the file paths are being changed, alter the file to reflect the changes.

Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:


STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "DG9A" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100

  GROUP 1 '/u03/oradata/dg9a/redo01.log'  SIZE 100M,
MAXINSTANCES 1
    MAXLOGHISTORY 453
LOGFILE

  GROUP 3 '/u03/oradata/dg9a/redo03.log'  SIZE 100M
GROUP 2 '/u03/oradata/dg9a/redo02.log'  SIZE 100M,
DATAFILE

  '/u03/oradata/dg9a/system01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',
'/u03/oradata/dg9a/undotbs01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',

  '/u03/oradata/dg9a/drsys01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',
'/u03/oradata/dg9a/cwmlite01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',
  '/u03/oradata/dg9a/example01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',

  '/u03/oradata/dg9a/tools01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',
'/u03/oradata/dg9a/indx01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',
  '/u03/oradata/dg9a/odm01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',
  '/u03/oradata/dg9a/users01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',

  '/u03/oradata/dg9a/planner01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>'
'/u03/oradata/dg9a/xdb01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',
  '/u03/oradata/dg9a/andy01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',
  '/u03/oradata/dg9a/psstats01<span class="GRcorrect">.</<span class="GRcorrect">span</span>><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">dbf</span></span>',
CHARACTER SET WE8ISO8859P1

;

9. Add a new entry to oratab and source the environment

Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.

Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
```
<span class="GRcorrect">echo $ORACLE_SID

If this doesn't output the new database sid go back and investigate.
```

10. Create the a password file

Use the following command to create a password file (add an appropriate password to the end of it):

<<span class="GRcorrect">span</span> class="GRcorrect"><span class="GRnoSuggestion GRcorrect">orapwd</span></span> file=$<span class="GRcorrect">{</span>ORACLE_HOME<span class="GRcorrect">}</span>/<span class="GRcorrect"><span class="GRcorrect">dbs</span></span>/orapw$<span class="GRcorrect">{</span>ORACLE_SID} password=<your password>

11. Create the new control file(s)

Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:


<<span class="GRcorrect">span</span> class="GRcorrect"><span class="GRnoSuggestion GRcorrect">sqlplus</span></span> "/ as sysdba"
@/home/oracle/cr_<new database <span class="GRcorrect"><span class="GRcorrect">sid</span></span>>

If all goes to plan you will see the instance start and then the message 'Control file created'.

12. Recover and open the database

The archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplurecover database using backup controlfile until cancel


> When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs:
 
<<span class="GRcorrect">span</span> class="GRcorrect">alter</span> database open resetlogs;
 

13. Create temp files< > Using the 'ALTER TABLESPACE TEMP...' command from step 8, create the temp files. Make sure the paths to the file(s) are correct, then run it from <span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">sqlplus</span></span>.
>
14. Perform a few checks</span>
> If the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:
>
** Check that the database has opened with:
<<span class="GRcorrect">span</span> class="GRcorrect">select</span> status from v$instance;
>> The status should be 'OPEN'
>>

> The new database will still have the source databases global name. Run the following to reset it:
** Make sure that the <span class="GRcorrect"><span class="GRnoSuggestion GRcorrect">datafiles</span></span> are all ok:
<<span class="GRcorrect">span</span> class="GRcorrect">select</span> distinct status from v$datafile;
>> It should return only ONLINE and SYSTEM.
>>
** Take a quick look at the alert log too.
15. Set the databases global name</span>

 
<<span class="GRcorrect">span</span> class="GRcorrect">alter</span> database rename global_name to <new database <span class="GRcorrect"><span class="GRcorrect">sid</span></span>>
/
Note. <<span class="GRcorrect">span</span> class="GRcorrect">no</span> quotes!

16. Create a spfile

From sqlplus:


<<span class="GRcorrect">span</span> class="GRcorrect">create</span> spfile from pfile;
17. Change the database ID

> If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
>
> From <<span class="GRcorrect">span</span> class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">sqlplus</span></span>:
<<span class="GRcorrect">span</span> class="GRcorrect">shutdown</span> immediate

From unix:</span>
<<span class="GRcorrect">span</span> class="GRcorrect">startup</span> <span class="GRcorrect">mount</span>

<<span class="GRcorrect">span</span> class="GRcorrect">exit</span>


<<span class="GRcorrect">span</span> class="GRcorrect"><span class="GRcorrect">nid</span></span> target=/

NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in <span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">sqlplus</span></span>:</span>


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';">shutdown immediate
<<span class="GRcorrect">span</span> class="GRcorrect">startup</span> <span class="GRcorrect">mount</span>

</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>
<<span class="GRcorrect">span</span> class="GRcorrect">alter</span> database open resetlogs

/

18. Configure TNS

Add entries for new database in the listener.ora and tnsnames.ora as necessary.
19. Finished

That's it!

=======================================

Clone an Oracle database using RMAN duplicate (same server)

tnsManager - Distribute tnsnames the easy way and for free!

This procedure will clone a database onto the same server using RMAN duplicate.

1. Backup the source database.

To use RMAN duplicate an RMAN backup of the source database is required. If there is already one available, skip to step 2. If not, here is a quick example of how to produce an RMAN backup. This example assumes that there is no recovery catalog available:


<<span class="GRcorrect">span</span> class="GRcorrect"><span class="GRcorrect">rman</span></span> target sys@<source database> <span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">nocatalog</span></span>
<<span class="GRcorrect">span</span> class="GRcorrect">backup</span> database plus archivelog format '/u01/ora_backup/<span class="GRcorrect"><span class="GRcorrect">rman</span></span>/%d_%u_%s';

This will backup the database and archive logs. The format string defines the location of the backup files. Alter it to a suitable location.

2. Produce a pfile for the new database

This step assumes that the source database is using a spfile. If that is not the case, simply make a copy the existing pfile.

Connect to the source database as sysdba and run the following:


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';">create pfile='init<new database <span class="GRcorrect"><span class="GRcorrect">sid</span></span>>.<<span class="GRcorrect">span</span> class="GRcorrect"><span class="GRnoSuggestion GRcorrect">ora</span></span>' from <span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">spfile</span></span>;
This will create a new <span class="GRcorrect"><span class="GRnoSuggestion GRcorrect">pfile</span></span> in the $ORACLE_HOME/<span class="GRcorrect"><span class="GRcorrect">dbs</span></span> directory.

Because in this example the cloned database will reside on the same machine as the source, Oracle must be told how convert the filenames during the RMAN duplicate operation. This is achieved by adding the following lines to the newly created <span class="GRcorrect"><span class="GRnoSuggestion GRcorrect">pfile</span></span>:</span>

The new <span class="GRcorrect"><span class="GRnoSuggestion GRcorrect">pfile</span></<span class="GRcorrect">span</span>> will need to be edited immediately. If the cloned database is to have a different name <span class="GRcorrect">to</span> the source, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary.


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';">db_file_name_convert=<span class="GRcorrect">(</span><source_db_path>,<target_db_path>)
log_file_name_convert=<span class="GRcorrect">(</span><source_db_path>,<target_db_path>)

</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>

Here is an example where the source database scr9 is being cloned to dg9a. Note the trailing <span class="GRcorrect">slashes</span> and lack of quotes:


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="background-color: #000000; color: #bbbbbb; font-family: 'Courier New';">db_file_name_convert=<span class="GRcorrect">(</span>/u01/oradata/scr9/<span class="GRcorrect">,</span>/u03/oradata/dg9a/)
log_file_name_convert=<span class="GRcorrect">(</span>/u01/oradata/scr9/<span class="GRcorrect">,</span>/u03/oradata/dg9a/)

</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>

3. Create bdump, udump & cdump directories

Create bdump, udump & cdump directories as specified in the pfile from the previous step.

4. Add a new entry to oratab, and source the environment

Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.

Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';">echo $ORACLE_SID

If this doesn't output the new database <span class="GRcorrect"><span class="GRcorrect">sid</span></span> go back and investigate why not.

</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>

5. Create a password file

Use the following command to create a password file (add an appropriate password to the end of it):


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';"><span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">orapwd</span></span> file=$<span class="GRcorrect">{</span>ORACLE_HOME<span class="GRcorrect">}</span>/<span class="GRcorrect"><span class="GRcorrect">dbs</span></span>/orapw$<span class="GRcorrect">{</span>ORACLE_SID} password=<your password>

</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>

6. Duplicate the database

From sqlplus, start the instance up in nomount mode:


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';">startup nomount

Exit <span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">sqlplus</span></span>, start RMAN and duplicate the database. As in step 1, it is assumed that no recovery catalog is available. If one is available, simply amend the RMAN command to include it.</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';"><span class="GRnoSuggestion GRcorrect"><span class="GRcorrect">rman</span></span> target sys@<source_database> <span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">nocatalog</span></span> auxiliary /
<<span class="GRcorrect">span</span> class="GRcorrect">duplicate</span> target database to <clone database name>;
This will restore the database and apply some archive logs. It can appear to hang at the end sometimes. Just give it time - I think it is because RMAN does a 'shutdown normal'.

If you see the following error, it is probably due to the file_name_convert settings being wrong. Return to step 2 and double check the settings.</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: red; font-family: 'Courier New';">RMAN-05001: auxiliary filename '%s' conflicts with a file used by the target database

Once the duplicate has finished RMAN will display a message similar to this:</span>


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="background-color: #000000; color: #bbbbbb; font-family: 'Courier New';">database opened
Finished Duplicate <span class="GRcorrect"><span class="GRcorrect">Db</span></span> at 26-FEB-05
RMAN>
Exit RMAN.

</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>

7. Create an spfile

From sqlplus:


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';">create spfile from pfile;
<<span class="GRcorrect">span</span> class="GRcorrect">shutdown</span> immediate

Now that the clone is built, we no longer need the file_name_convert settings:</span>

<<span class="GRcorrect">span</span> class="GRcorrect">startup</span>


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';">alter system reset db_file_name_convert scope=spfile sid='*'
/
<<span class="GRcorrect">span</span> class="GRcorrect">alter</span> system <span class="GRcorrect">reset</span> log_file_name_convert scope=spfile sid='*'
/

</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>

8. Optionally take the clone database out of archive log mode

RMAN will leave the cloned database in archive log mode. If archive log mode isn't required, run the following commands from sqlplus:


<<<span class="GRcorrect">span</span> class="GRcorrect">span</span> style="color: blue; font-family: 'Courier New';">shutdown immediate
<<span class="GRcorrect">span</span> class="GRcorrect">startup</span> <span class="GRcorrect">mount</span>

<<span class="GRcorrect">span</span> class="GRcorrect">alter</span> database <span class="GRnoSuggestion GRcorrect"><span class="GRnoSuggestion GRcorrect">noarchivelog</span></span>;
<<span class="GRcorrect">span</span> class="GRcorrect">alter</span> database open;

</<<span class="GRcorrect">span</span> class="GRcorrect">span</span>>

9. Configure TNS

Add entries for new database in the listener.ora and tnsnames.ora as necessary.

CLOUDARCHITECT

blog menu1

Create Duplicate DB using Cold, Hot RMAN backup

Create Duplicate DB using Cold, Hot RMAN backup

Clone an Oracle database using an online/hot backup

=======================================

Clone an Oracle database using RMAN duplicate (same server)

No comments:

Post a Comment