Data | database
About cloning the database method, has been studying recently, occasionally wandering on the cnoug, found two posts good, now as a reference in the future work:
Method One:
How to use Rman to clone a database between two machines
The target database and the clone database are in both machines, and their environment is the same:
WINDOWS SERVER SP4, IE6.1, ORACLE9205
In this article, the Rman on the target database is used, and the directory database is on the 3rd machine,
Operation Steps:
1, connect to the target database, the target database to do a full library backup
D:\oracle\bin>rman
Recovery Manager: Version 9.2.0.5.0-production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Rman> Connect Catalog rman/rman@187
Connecting to the Recovery directory database
Rman> Connect Target/
Connect to target database: ENCORE (dbid=3146513770)
Rman> Run
2> {Allocate channel D1 type disk maxpiecesize = 500m;
3> Backup full Database
4> format ' E:\rmanbak\db_%d_%s_%p_%t ';
5> release channel D1;
6>}
Start Backup at 2004-06-28 21:03:31
Channel D1: Starting full data file backup set
Channel D1: Specifying Data files in Backup set
Include the current SPFILE in the backup set
The backup set includes the current control file
Enter the data file fno=00001 name=d:\oracle\oradata\encore YSTEM01. Dbf
Enter the data file fno=00002 name=d:\oracle\oradata\encore\undotbs01. Dbf
Enter the data file fno=00005 name=d:\oracle\oradata\encore\example01. Dbf
Enter the data file fno=00008 name=d:\oracle\oradata\encore\tools01. Dbf
Enter the data file fno=00010 name=d:\oracle\oradata\encore\xdb01. Dbf
Enter the data file fno=00006 name=d:\oracle\oradata\encore\indx01. Dbf
Enter the data file fno=00009 name=d:\oracle\oradata\encore\users01. Dbf
Enter the data file fno=00003 name=d:\oracle\oradata\encore\cwmlite01. Dbf
Enter the data file fno=00004 name=d:\oracle\oradata\encore\drsys01. Dbf
Enter the data file fno=00007 name=d:\oracle\oradata\encore\odm01. Dbf
Channel D1: Starting segment 1 at 2004-06-28 21:03:37
Channel D1: Completed segment 1 at 2004-06-28 21:05:02
Duan handle=e:\rmanbak\db_encore_1_1_530053412 Comment=none
Channel D1: Starting segment 2 at 2004-06-28 21:05:02
Channel D1: Completed segment 2 at 2004-06-28 21:06:07
Duan handle=e:\rmanbak\db_encore_1_2_530053412 Comment=none
Channel D1: Backup set completed, after time: 00:02:35
Completing backup at 2004-06-28 21:06:07
Channel Freed: D1
Rman>
2, backup the target database parameters file
Sql> create Pfile from SPFile;
File is created
Sql>
3 、--Copy the parameter file of the target database (created in step 2nd) to the clone machine and modify the corresponding parameters (mainly the instance name and
Database name, and Bdump, Udump, Cdump, and other background process tracking files stored in the path, in this article, the clone database
Instance name is clone, database name is clone_db, and then the instance is created on the clone machine
5, modify the Tnsnames.ora file on the Rman machine, add this connection string to ensure that Rman use this connection string to connect to
Cloning an instance of a database
5. Copy the backup set of the target database to the same location as the Clone database machine and start the Clone database (Nomount state)
6, because the 1th step does not have a backup log, in the use of
Rman> Run
2> {Allocate auxiliary channel D1 type disk;
3> set newname for datafile 1 to ' F:\CLONE\data YSTEM01. DBF ';
When the script clones the database, there are errors such as NO BACKUP COPY,
Later, you supplement the log of the target database.
Rman> Run
2> {Allocate channel D1 type disk maxpiecesize = 500m;
3> Backup Archivelog All
4> format ' E:\rmanbak\log_%d_%s_%p_%t ';
5> release channel D1;
6>}
Start Backup at 2004-06-28 21:42:18
The current log is archived
Channel D1: Starting archive log backup set
Channel D1: Specifying archive logs in Backup set
Enter archive log thread =1 sequence = 107 record id=107 timestamp =528371132
Enter archive log thread =1 sequence = 108 record id=108 timestamp =530053839
Enter archive log thread =1 sequence = 109 record id=109 timestamp =530054855
Enter archive log thread =1 sequence = 110 record id=110 timestamp =530055437
Enter archive log thread =1 sequence = 111 record id=111 timestamp =530055540
Enter archive log thread =1 sequence = 112 record id=112 timestamp =530055739
Channel D1: Starting segment 1 at 2004-06-28 21:42:23
Channel D1: Completed segment 1 at 2004-06-28 21:42:26
Duan handle=e:\rmanbak\log_encore_2_1_530055742 Comment=none
Channel D1: Backup set completed, after time: 00:00:04
Completing backup at 2004-06-28 21:42:26
Channel Freed: D1
7. Copy the log backup set to the same directory of the clone machine, return to the Rman, connect to the auxiliary (clone) database,
Execute the script, and the rest of the Rman will do it himself
Rman> Connect Auxiliary Sys/sys1@clone
Connected to standby Database: clone_db (not installed)
Rman> Run
2> {Allocate auxiliary channel D1 type disk;
3> set newname for datafile 1 to ' F:\CLONE\data YSTEM01. DBF ';
4> set newname for datafile 2 to ' F:\CLONE\data\UNDOTBS01. DBF ';
5> set newname for datafile 3 to ' F:\CLONE\data\CWMLITE01. DBF ';
6> set newname for datafile 4 to ' F:\CLONE\data\DRSYS01. DBF ';
7> set newname for datafile 5 to ' F:\CLONE\data\EXAMPLE01. DBF ';
8> set newname for datafile 6 to ' F:\CLONE\data\INDX01. DBF ';
9> set newname for datafile 7 to ' F:\CLONE\data\ODM01. DBF ';
10> set newname for datafile 8 to ' F:\CLONE\data\TOOLS01. DBF ';
11> set newname for datafile 9 to ' F:\CLONE\data\USERS01. DBF ';
12> set newname for DataFile to ' F:\CLONE\data\XDB01. DBF ';
13> DUPLICATE TARGET DATABASE to clone_db
14> LOGFILE
15> GROUP 1 (' F:\clone\data\REDO01. DBF ') SIZE 10M,
16> GROUP 2 (' F:\clone\data\REDO02. DBF ') SIZE 10M,
17> GROUP 3 (' F:\clone\data\REDO03. DBF ') SIZE 10M;
18>}
Printing stored scripts: Memory script
{
Set until SCN 261688166;
Set newname for DataFile 1 to
"F:\CLONE\DATA YSTEM01. DBF ";
Set newname for DataFile 2 to
"F:\CLONE\DATA\UNDOTBS01. DBF ";
Set newname for DataFile 3 to
"F:\CLONE\DATA\CWMLITE01. DBF ";
Set newname for DataFile 4 to
"F:\CLONE\DATA\DRSYS01. DBF ";
Set newname for DataFile 5 to
"F:\CLONE\DATA\EXAMPLE01. DBF ";
Set newname for DataFile 6 to
"F:\CLONE\DATA\INDX01. DBF ";
Set newname for datafile 7 to
"F:\CLONE\DATA\ODM01. DBF ";
Set newname for DataFile 8 to
"F:\CLONE\DATA\TOOLS01. DBF ";
Set newname for DataFile 9 to
"F:\CLONE\DATA\USERS01. DBF ";
Set newname for DataFile
"F:\CLONE\DATA\XDB01. DBF ";
Restore
Check ReadOnly
Clone database
;
}
Executing script: 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
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Start restore at 2004-06-28 21:43:54
Channel D1: Starting recovery of data file backup set
Channel D1: Specifying a data file to recover from a backup set
Restoring data file 00001 to F:\CLONE\DATA YSTEM01. Dbf
Restoring data file 00002 to F:\CLONE\DATA\UNDOTBS01. Dbf
Restoring data file 00003 to F:\CLONE\DATA\CWMLITE01. Dbf
Restoring data file 00004 to F:\CLONE\DATA\DRSYS01. Dbf
Restoring data file 00005 to F:\CLONE\DATA\EXAMPLE01. Dbf
Restoring data file 00006 to F:\CLONE\DATA\INDX01. Dbf
Restoring data file 00007 to F:\CLONE\DATA\ODM01. Dbf
Restoring data file 00008 to F:\CLONE\DATA\TOOLS01. Dbf
Restoring data file 00009 to F:\CLONE\DATA\USERS01. Dbf
Restoring data file 00010 to F:\CLONE\DATA\XDB01. Dbf
Channel D1: restored backup Segment 1
Duan handle=e:\rmanbak\db_encore_1_1_530053412 tag=tag20040628t210331 params=null
Channel D1: restored backup segment 2
Duan handle=e:\rmanbak\db_encore_1_2_530053412 tag=tag20040628t210331 params=null
Channel D1: Recovery complete
Completing restore at 2004-06-28 21:46:35
SQL statement: CREATE controlfile reuse SET DATABASE "clone_db" Resetlogs Archivelog
Maxlogfiles 50
Maxlogmembers 5
Maxdatafiles 100
Maxinstances 1
Maxloghistory 226
LOGFILE
GROUP 1 (' F:\clone\data\REDO01. DBF ') SIZE 10485760,
GROUP 2 (' F:\clone\data\REDO02. DBF ') SIZE 10485760,
GROUP 3 (' F:\clone\data\REDO03. DBF ') SIZE 10485760
DataFile
' F:\CLONE\DATA YSTEM01. DBF '
CHARACTER SET ZHS16GBK
Data file 2 has been converted to a copy of the data file
Enter the data file copy recid=1 stamp=530056505 filename =f:\clone\data\undotbs01. Dbf
Data file 3 has been converted to a copy of the data file
Enter the data file copy recid=2 stamp=530056505 filename =f:\clone\data\cwmlite01. Dbf
Data file 4 has been converted to a copy of the data file
Enter the data file copy recid=3 stamp=530056505 filename =f:\clone\data\drsys01. Dbf
Data file 5 has been converted to a copy of the data file
Enter the data file copy recid=4 stamp=530056505 filename =f:\clone\data\example01. Dbf
Data file 6 has been converted to a copy of the data file
Enter the data file copy recid=5 stamp=530056505 filename =f:\clone\data\indx01. Dbf
Data File 7 has been converted to a copy of the data file
Enter the data file copy recid=6 stamp=530056505 filename =f:\clone\data\odm01. Dbf
Data File 8 has been converted to a copy of the data file
Enter the data file copy recid=7 stamp=530056505 filename =f:\clone\data\tools01. Dbf
Data file 9 has been converted to a copy of the data file
Enter the data file copy recid=8 stamp=530056505 filename =f:\clone\data\users01. Dbf
Data file 10 has been converted to a copy of the data file
Enter the data file copy recid=9 stamp=530056505 filename =f:\clone\data\xdb01. Dbf
A copy of the data file has been included in the directory
Data file copy filename=f:\clone\data\undotbs01. DBF recid=1 stamp=530056530
A copy of the data file has been included in the directory
Data file copy filename=f:\clone\data\cwmlite01. DBF recid=2 stamp=530056530
A copy of the data file has been included in the directory
Data file copy filename=f:\clone\data\drsys01. DBF recid=3 stamp=530056531
A copy of the data file has been included in the directory
Data file copy filename=f:\clone\data\example01. DBF recid=4 stamp=530056531
A copy of the data file has been included in the directory
Data file copy filename=f:\clone\data\indx01. DBF recid=5 stamp=530056531
A copy of the data file has been included in the directory
Data file copy FILENAME=F:\CLONE\DATA\ODM01. DBF recid=6 stamp=530056531
A copy of the data file has been included in the directory
Data file copy filename=f:\clone\data\tools01. DBF recid=7 stamp=530056532
A copy of the data file has been included in the directory
Data file copy filename=f:\clone\data\users01. DBF recid=8 stamp=530056532
A copy of the data file has been included in the directory
Data file copy FILENAME=F:\CLONE\DATA\XDB01. DBF recid=9 stamp=530056532
Data file 2 has been converted to a copy of the data file
Enter the data file copy recid=1 stamp=530056530 filename =f:\clone\data\undotbs01. Dbf
Data file 3 has been converted to a copy of the data file
Enter the data file copy recid=2 stamp=530056530 filename =f:\clone\data\cwmlite01. Dbf
Data file 4 has been converted to a copy of the data file
Enter the data file copy recid=3 stamp=530056531 filename =f:\clone\data\drsys01. Dbf
Data file 5 has been converted to a copy of the data file
Enter the data file copy recid=4 stamp=530056531 filename =f:\clone\data\example01. Dbf
Data file 6 has been converted to a copy of the data file
Enter the data file copy recid=5 stamp=530056531 filename =f:\clone\data\indx01. Dbf
Data File 7 has been converted to a copy of the data file
Enter the data file copy recid=6 stamp=530056531 filename =f:\clone\data\odm01. Dbf
Data File 8 has been converted to a copy of the data file
Enter the data file copy recid=7 stamp=530056532 filename =f:\clone\data\tools01. Dbf
Data file 9 has been converted to a copy of the data file
Enter the data file copy recid=8 stamp=530056532 filename =f:\clone\data\users01. Dbf
Data file 10 has been converted to a copy of the data file
Enter the data file copy recid=9 stamp=530056532 filename =f:\clone\data\xdb01. Dbf
Printing stored scripts: Memory script
{
Alter Clone database open resetlogs;
}
Executing script: Memory script
Database is open
Rman>
--This step requires attention: Avoid connecting to the clone database, otherwise Rman will not shut down the clone database properly
(Shutdown clone), the remaining steps cannot continue.
8. Test Clone database
D:\oracle\bin>sqlplus
Sql*plus:release 9.2.0.5.0-production on Mon June 28 22:01:46 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter User-name:sys/sys1@clone as Sysdba
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0-production
With the partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
Jserver Release 9.2.0.5.0-production
Summary: In the two machines with Rman clone database, personally think, there are two points to note:
1, in the target database to do a full library backup, pay attention to the backup log at the same time, the lack of this step, Rman will be in the cloning of no BACKUP copy of the error.
2. To copy the backup set of the target database back to the same directory of the clone machine (the directory where the backup set resides when the target database is backed up), Rman will report that the XXXX backup set cannot be found.
DBCA is a tool that allows you to configure, manage, and build a database. During the initial database installation, if you select "Create a database option", this tool will start and run. You can build a database based on the template, or you can import a new template from the previous example.
Many managers don't know, just type DBCA on the command line to get into the DBCA environment. Once this tool is started, you can create a new database, configure the Init.ora option for the database, delete an existing database, or manage the database template. These database templates are similar to the templates generated at installation time (such as general purpose, Data Warehouse, and transaction processing).
During the installation process, a warning dialog box appears asking if you want to save your template. You can click No, or you can manually define your database at the time of installation. The DBCA tool can check database settings and generate a template based on the current operating system, or even selectively output current data. This feature makes it an excellent tool for moving a database to another machine or embedding a database into other valuable products.
The DBCA tool stores the template in a file with a. dbc extension. A. dbc file contains a list of Init.ora parameters, location data files, log files, and control files in XML format in the database. If you choose to include your database data, this tool will also generate a file with an extension of. DBJ, a zip-compressed file that contains an image of your data file. Some users would think of setting up a backup of all these settings to avoid the need to re-establish a database on a new machine.
Method Two:
Cloning a database using DBCA
There are many variables that allow DBCA to install files to the appropriate path for ORACLE software, such as {oracle_base} and {db_name}. Since these are just simple XML, you can use a diff function to quickly see the differences between these templates. For example, the difference between the general purpose and the Data Warehouse database template is the Data Warehouse template for Oracle 9i 9.2.0.0:
TEMP tablespace is less than 1M.
The Init.ora parameters include:
Star_transformation_enabled is activated.
Pga_aggregate_target comparison in.
Query_rewrite_enabled is activated.
Db_file_multiblock_read_count is doubled.
Sort_area_size is doubled.
Hash_area_size is not set.
Db_cache_size is smaller than the other.
For transaction processing templates, the difference is:
The Oracle OLAP options are not activated.
The Init.ora parameters include:
Db_block_size is half.
Undo_retention is smaller than the other.
Pga_aggregate_target is smaller than the other.
Db_file_multiblock_read_count is half.
Hash_join_enabled is closed.
Db_cache_size is relatively large.
There is no need to create a cwmlite tablespace (because the OLAP option has been turned off).
EXAMPLE and SYSTEM tablespaces are smaller.
TEMP tablespace is double size.
When you use the DBCA tool to output data from a database, DBCA generates a file with a. dfj extension, a zip-compressed file that contains the source image for each data file. This means that the data ensures security during the transfer process. However, these files are not well documented and supported by EXP and Imp. Again, most of these images are written directly to the disk, not like IMP's through SQL writes.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
