User-managed database clone

Oracle Database replication or cloning is often used to provide a test or development environment. There are multiple methods to generate a clone database, such as using the cold standby mode for database cloning (you need to use NID to modify db_name), hot standby mode for database cloning, and RMAN mode for database cloning. Because the database is cloned on the same machine, the target database and the original database must be located in different directories. Second, the Database Name (db_name) is not used ). This document describes how to clone a database using a user-managed hot standby mode and provides an example.

 

1. Hot Standby cloning procedure
A. Create the target database directory
B. Create the target database password file (orapwd)
C. Create a parameter file for the target database (pfile/spfile)
D. Back up the original database and copy the backup file to the target database.
E. Start the target database to the nomount status and create a control file.
F. Restore the target database (recover)
G. Open with resetlogs)
H. Check the database and add temporary data files

 

2. Demonstrate Hot Standby clone Database

--> Demo environment SQL> Ho CAT/etc/issueenterprise Linux Enterprise Linux Server Release 5.5 (Carthage) kernel \ r on an \ msql> select * from V $ version where rownum <2; banner login Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-productionsql> select name, log_mode, open_mode from V $ database; Name log_mode open_mode ---------------- ----------- ------------ ------------------ Sybo archivelog read write -- original database name: sybo3 -- target database name: sybo4 -- original database Directory:/u01/database/sybo3 -- target database directory: /u01/database/sybo4 -- A. Create the target database directory [Oracle @ linux3 database] $ more sybo4.sh #! /Bin/shmkdir-P/u01/databasemkdir-P/u01/database/sybo4/adumpmkdir-P/u01/database/sybo4/controlfmkdir-P/u01/database/sybo4/logs -P/u01/database/sybo4/oradatamkdir-P/u01/database/sybo4/redomkdir-P/u01/database/sybo4/dpdumpmkdir-P/u01/database/sybo4/pfilemkdir -P/u01/database/sybo4/db_broker [Oracle @ linux3 database] $. /sybo4.sh -- B. Create the target Database Password File $ orapwd file = $ ORACLE_HOME/dbs/orapwsybo4 Password = Oracle entries = 10 -- C. Create the target database parameter file -- generate the target database from the original database SQL> Create pfile = '/u01/Oracle/db_1/dbs/initsybo4.ora' from spfile; -- modify the target database parameter file $ sed-I's/sybo3/sybo4/G' $ ORACLE_HOME/dbs/initsybo4.ora $ grep sybo3 $ ORACLE_HOME/dbs/initsybo4.ora --> check whether the file still exists sybo3-the final target database parameter file $ more $ ORACLE_HOME/dbs/initsybo4.orasybo4. _ db_cache_size = 117440512sybo4. _ java_pool_size = 4193474sybo4. _ large_pool_size = 4192134sybo4. _ oracle_base = '/u01/Oracle' # oracle_base set from environmentsybo4. _ pga_aggregate_target = 150994944sybo4. _ sga_target = 22682416sybo4. _ shared_io_pool_size = 0sybo4. _ shared_pool_size = 92274688sybo4. _ streams_pool_size = 0 *. audit_file_dest = '/u01/database/sybo4/adump /'*. audit_trail = 'db '*. compatible = '11. 2.0.0.0 '*. control_files = '/u01/database/sybo4/controlf/control01.ctl', '/u01/database/sybo4/controlf/control02.ctl '*. db_block_size = 8192 *. db_domain = 'orasrv. com '*. db_name = 'sybo4 '*. db_recovery_file_dest = '/u01/database/sybo4/flash_recovery_area /'*. db_recovery_file_dest_size = 4039114752 *. dg_broker_config_file1 = '/u01/database/sybo4/db_broker/dr1sybo4. dat '*. dg_broker_config_file2 = '/u01/database/sybo4/db_broker/dr2sybo4. dat '*. dg_broker_start = false *. diagnostic_dest = '/u01/database/sybo4 '*. log_archive_dest_1 = ''*. memory_target = 374341632 *. open_cursors = 300 *. processes = 150 *. remote_login_passwordfile = 'clusive '*. undo_tablespace = 'undotbs1 '-- d. Back up the original database and copy the backup file to the target database -- create a temporary table t. The user verifies whether the cloning is successful. SQL> Create Table T (name varchar2 (10), action varchar2 (20); SQL> insert into T select 'robinson ', 'transfer db' from dual; SQL> commit; SQL> alter system archive log current; -- prepare a control file script for the target database. the trace file is located in the SQL> alter Database Backup controlfile to trace resetlogs under the user_dump_dest directory. -- back up the original database. If there are many database files, use the Hot Standby script to complete SQL> alter database begin backup; -- copy the database file to the target database directory SQL> host CP/u01/database/sybo3/oradata/*/u01/database/sybo4/oradatasql> alter Database End backup; -- e. Start the target database to the nomount State and create the control file $ export oracle_sid = sybo4 $ sqlplus/As sysdbasql> startup nomount pfile =/u01/Oracle/db_1/dbs/initsybo4.ora; oracle instance started. SQL> Get sybo4ctl. SQL 1 create controlfile set Database "sybo4" resetlogs archivelog 2 maxlogfiles 16 3 maxlogmembers 3 4 maxdatafiles 100 5 maxinstances 8 6 maxloghistory 292 7 logfile 8 Group 1 '/u01/database/sybo4/Redo /redo01.log 'size 50 m blocksize 512, 9 Group 2'/u01/database/sybo4/Redo/redo02.log 'size 50 m blocksize 512, 10 group 3'/u01/database/sybo4/Redo/redo03.log 'size 50 m blocksize 512 11 datafile 12'/u01/database/sybo4/oradata/system01.dbf ', 13 '/u01/database/sybo4/oradata/sysaux01.dbf', 14'/u01/database/sybo4/oradata/undotbs01.dbf ', 15'/u01/database/sybo4/oradata/users01.dbf', 16'/u01/database/sybo4/oradata/example01.dbf' 17 Character Set al32utf8 18 *; SQL> @ sybo4ctl. sqlcontrol file created. SQL> alter database Mount; --> note that after the control file is created, the database has been mounted, as follows we have received the error prompt alter database Mount * error at line 1: ORA-01100: database already mounted -- we modified the Control File Script above, use the set database and resetlogs methods to create a database -- F. Restore the target database SQL> set logsource '/u01/database/sybo3/flash_recovery_area/sybo3/archivelog/2013_07_24 '; SQL> recover database using backup controlfile until cancel; ORA-00279: Change 847086 generated at 07/24/2013 14:42:06 needed for thread 1ora-00289: Suggestion: /u01/database/sybo3/flash_recovery_area/sybo3/archivelog/2013_07_24/o1_mf_1_7_821617241.dbfORA-00280: Change 847086 for thread 1 is in sequence #7 specify log: {<RET> = suggested | filename | auto | cancel}/u01/database/sybo3/Redo/redo01.loglog applied. media recovery complete. -- G. Open the target database SQL> alter database open resetlogs; database altered. -- H. Check the database and add a temporary data file SQL> select * from t; Name action ---------- ------------------ Robinson transfer dbsql> select name from V $ datafile; name users/u01/database/sybo4/oradata/system01.dbf/u01/database/sybo4/oradata/sysaux01.dbf/u01/database/sybo4/oradata/users/u01/database/sybo4/oradata /users01.dbf/u01/database/sybo4/oradata/example01.dbfsql> Col member format a60sql> select Member from V $ logfile; member logs/u01/database/sybo4/Redo/redo03.log/u01/database/sybo4/Redo/redo02.log/u01/database/sybo4/Redo/redo01.logsql> select name from V $ controlfile; name controls/u01/database/sybo4/controlf/control01.ctl/u01/database/sybo4/controlf/control02.ctl -- Author: Robinson -- Blog: http://blog.csdn.net/robinson_0612SQL> select * from V $ tempfile; no rows selectedsql> select property_name, property_value from database_properties where property_name like '% default % '; property_name property_value ------------------------------------------------------------ ult_temp_tablespace tempdefault_permanent_tablespace usersdefault_edition ora $ basedefault_tbs_type smallfilesql> select tablespace_name from dba_tablespaces where tablespace_name = 'temp '; TABLESPACE_NAME------------------------------TEMPSQL> alter tablespace temp add tempfile '/u01/database/sybo4/oradata/tempfile. DBF 'size 50 m autoextend on; -- create a server parameter file. We recommend that you disable the database consistently, back up the database, and add the database to/etc/oratab, configure the listener and other SQL> Create spfile from pfile;

3. Summary
A. Cloning a user-based hot standby database is similar to creating a new database, because we need to prepare all the processes required to create the entire database.
B. Understand the Oracle database startup steps (nomount, mount, open) and the relevant files required for each step and the actions completed at different stages. For details, refer to the Oracle database instance startup and shutdown process.
C. Understand the functions of different types of files, such as Oracle parameter files, Oracle password files, Oracle control files, and eventually opened database files.
D. For the Hot Standby database to be copied to the target database directory, it is equivalent to the restoration operation, that is, the restore operation of RMAN.
E. The database recovery operation uses the using backup controlfile method because the control file is inconsistent with the data file. For more information, see using backup controlfile.
F. Because the archive log is located at the archive location of the original database, the set logsource clause is used to specify the location of the archived log during recovery.
G. when creating the control file, because it is a new DB, you must use the resetlog method, otherwise you will receive the ORA-01223: resetlogs must be specified to set a new database name

 

 

References
Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

Oracle backup control file recovery (unsing backup controlfile)

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

RMAN backup path confusion

Customize the date and time format displayed by RMAN

Backup and recovery of read-only tablespace

Incomplete recovery of Oracle based on user management

Understanding using backup controlfile

Use RMAN for recovery from different machine backups (WIN platform)

Use RMAN to migrate a file system database to ASM

Oracle backup policy (RMAN) in Linux)

Linux RMAN backup shell script

Use RMAN to migrate the database to a different machine

Run the SQL statement at the RMAN prompt.

Oracle RMAN-based Incomplete recovery (Incomplete recovery by RMAN)