Oracle Study Notes: Create a clone database by using RMAN Database Backup

I have paid a lot of effort to improve it ........ You only need to define the following basic variables in the batch processing to generate clone instances of any Sid to any directory.

Set oracle_base = D: \ Oracle10g \ app \ oracle \ product \ 10.2.0
Set oracle_sid = temp
Set syspwd = Oracle
Set data_base = D: \ data

Not blow: study my scripts and you will get a lot!

 

Known problems: you must understand the backup content before you can write a script that suits you! I created it based on the default Oracle seed template!

 

It consists of three parts:

1. Batch Processing-used to set variables, run commands, dynamically generate scripts, and run scripts

2. Batch Processing dynamically generated scripts-define replacement Variables

3. Fixed scripts-extract database files from backups, generate control files, and open databases

Batch Processing:

@ Echo off
Echo.
Echo Step1: Set Environment Variables
Set oracle_base = D: \ Oracle10g \ app \ oracle \ product \ 10.2.0
Set oracle_sid = temp
Set syspwd = Oracle
Set data_base = D: \ data

Set datapath = % data_base % \ % oracle_sid %
Set ORACLE_HOME = % oracle_base % \ Server
Set Path = % PATH %; % ORACLE_HOME % \ bin;
Set adminpath = % oracle_base % \ admin \ % oracle_sid %

Echo.
Echo step2: delete instance: Please wait...
Oradim-delete-Sid % oracle_sid %
Echo step2: instance deleted: successful!

Echo.
Echo Step3: Delete the existing db_create_file_dest directory and * dump directory of the % oracle_sid % instance.
If exist % datapath % del % datapath % \ *. */S/f/Q
If exist % adminpath % del % adminpath % \ *. */S/f/Q
If exist % ORACLE_HOME % \ database \ init % oracle_sid %. ora del % ORACLE_HOME % \ database \ init % oracle_sid %. ora
If exist % ORACLE_HOME % \ database \ PWD % oracle_sid %. ora del % ORACLE_HOME % \ database \ PWD % oracle_sid %. ora
Rem del % ORACLE_HOME % \ database \ create_db.dbf

echo.
echo step4: create the necessary directory for % oracle_sid % instance
if not exist % ORACLE_HOME % \ export toollogs \ dbca \ % oracle_sid % mkdir % ORACLE_HOME % \ export toollogs \ dbca \ % oracle_sid %
if not exist % ORACLE_HOME % \ DBS mkdir % ORACLE_HOME % \ DBS
if not exist % ORACLE_HOME % \ RDBMS \ trace mkdir % ORACLE_HOME % \ RDBMS \ trace
if not exist % ORACLE_HOME % \ RDBMS \ log mkdir % ORACLE_HOME % \ RDBMS \ log
if not exist % adminpath % \ adump mkdir % adminpath % \ adump
if not exist % adminpath % \ bdump mkdir % adminpath % \ bdump
if not exist % adminpath % \ cdump mkdir % adminpath % \ cdump
if not exist % adminpath % \ dpdump mkdir % adminpath % \ dpdump
if not exist % adminpath % \ pfile mkdir % adminpath % \ pfile
if not exist % adminpath % \ udump mkdir % adminpath % \ udump
If not exist % datapath % mkdir % datapath %

Echo.
Echo step5: Copy database backup file: % ORACLE_HOME % \ database \ create_db.dbf
If not exist % ORACLE_HOME % \ database \ create_db.dbf copy usercent-2009-02-23.dbf % ORACLE_HOME % \ database \ create_db.dbf

Echo.
Echo step6: Create % oracle_sid % instance initialization parameter file pfile: % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo compatible = '10. 2.0.1.0 '> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo db_name = '% oracle_sid %'> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo db_recovery_file_dest = '% data_base %'> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo db_recovery_file_dest_size = 2G> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo sga_target = 250 m> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo undo_management = 'auto'> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo control_files = '% datapath % \ Control _ % oracle_sid % _ 01. CTL ',' % datapath % \ Control _ % oracle_sid % _ 02. CTL '> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo audit_file_dest = '% adminpath % \ adump'> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo background_dump_dest = '% adminpath % \ bdump'> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo core_dump_dest = '% adminpath % \ cdump'> % ORACLE_HOME % \ database \ init % oracle_sid %. ora
Echo user_dump_dest = '% adminpath % \ udump'> % ORACLE_HOME % \ database \ init % oracle_sid %. ora

Echo.
Echo step7: Create % oracle_sid % instance and define the password of SYS user
Oradim.exe-New-Sid % oracle_sid %-syspwd %

Echo.
Echo step8: generate the create_db_define. SQL definition to replace the variable file using redirection
Echo define datapath = '% datapath %';>. \ create_db_define. SQL
Echo define oracle_sid = '% oracle_sid %'; >>. \ create_db_define. SQL
Echo define ORACLE_HOME = '% ORACLE_HOME %'; >>. \ create_db_define. SQL

Echo.
Echo step9: connect to the instance using sqlplus and run the script for creating the database.
Sqlplus/As sysdba @ % Cd % \ create_db_and_controlfile. SQL

Pause
@ Echo on

 

Fixed script:

@ Create_db_define. SQL;
Set verify off;
Set define on;
Startup nomount;
Declare
Devicename varchar2 (255 );
Omfname varchar2 (512): = NULL;
Done Boolean;
Begin
Devicename: = dbms_backup_restore.deviceallocate;
Dbms_backup_restore.restoresetdatafile;
Dbms_backup_restore.restoredatafileto (1, '& datapath \ system01.dbf ');
Dbms_backup_restore.restoredatafileto (2, '& datapath \ undotbs01.dbf ');
Dbms_backup_restore.restoredatafileto (3, '& datapath \ sysaux01.dbf ');
Dbms_backup_restore.restoredatafileto (4, '& datapath \ users01.dbf ');
Dbms_backup_restore.restorebackuppiece ('& ORACLE_HOME \ database \ create_db.dbf', done );
Dbms_backup_restore.devicedeallocate;
End;
/
Create controlfile reuse set Database "& oracle_sid"
Maxinstances 8
Maxloghistory 1
Maxlogfiles 16
Maxlogmembers 3
Maxdatafiles 100
Datafile
'& Datapath \ system01.dbf ',
'& Datapath \ undotbs01.dbf ',
'& Datapath \ sysaux01.dbf ',
'& Datapath \ users01.dbf'
Logfile
Group 1 ('& datapath \ redo01.log') size 51200 K,
Group 2 ('& datapath \ redo02.log') size 51200 K,
Group 3 ('& datapath \ redo03.log') size 51200 K
Resetlogs;

Alter database open resetlogs;

 

Dynamic script content:

Define datapath = 'd: \ data \ Temp ';
Define oracle_sid = 'temp ';
Define ORACLE_HOME = 'd: \ Oracle10g \ app \ oracle \ product \ 10.2.0 \ Server ';