User-managed database clone

User-managed on-premise database cloning Oracle on-premise 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 step a. Create the target database directory B. Create the target database password file (orapwd) c. Create the target database parameter file (pfile/spfile) d. Back up the original database, copy the backup file to the target database e, start the target database to the nomount state, create the control file f, and restore the target database (recover) g. open with resetlogs, verify the database, and add temporary data files. 2. Demonstrate Hot Standby clone database.

[SQL] --> demo environment SQL> ho cat/etc/issue Enterprise Linux Server release 5.5 (Carthage) kernel \ r on an \ m SQL> select * from v $ version where rownum <2; BANNER orders Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production SQL> 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/sh mkdir-p/u01/database mkdir-p/u01/database/sybo4/adump mkdir-p/u01/database/sybo4/controlf mkdir-p/u01/database /sybo4/flash_recovery_area mkdir-p/u01/database/sybo4/oradata mkdir-p/u01/database/sybo4/redo mkdir-p/u01/database/sybo4/dpdump mkdir-p /u01/database/sybo4/pfile mkdir-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-final target database parameter file $ more $ ORACLE_HOME/dbs/initsybo4.ora sybo4. _ runtime = 117440512 sybo4. _ java_pool_size = 4194304 sybo4. _ runtime = 4194304 sybo4. _ oracle_base = '/u01/oracle' # ORACLE_BASE set from environment sybo4. _ percent = 150994944 sybo4. _ sga_target = 226492416 sybo4. _ percent = 0 sybo4. _ percent = 92274688 sybo4. _ 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/oradata SQL> alter database end backup; -- e. Start the target database to the nomount State and create the control file $ export ORACLE_SID = sybo4 $ sqlplus/as sysdba SQL> 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. SQL Control 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 1 ORA-00289: suggestion: /u01/database/sybo3/flash_recovery_area/SYBO3/archivelog/2013_07_24/o1_mf_1_7_8216 17241.dbf ORA-00280: change 847086 for thread 1 is in sequence #7 Specify log: {<RET> = suggested | filename | AUTO | CANCEL}/u01/database/sybo3/redo/redo01.log Log 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 db SQL> 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.dbf SQL> col member format a60 SQL> select member from v $ logfile; MEMBER logs/u01/database/sybo4/redo/redo03.log/u01/database/sybo4/redo/redo02.log/u01/database/sybo4/redo/redo01.log SQL> select name from v $ controlfile; NAME users/u01/database/sybo4/controlf/control01.ctl/u01/database/sybo4/controlf/control02.ctl -- Author: Robinson SQL> select * from v $ tempfile; no rows selected SQL> select property_name, property_value from database_properties where property_name like '% DEFAULT % '; PROPERTY_NAME PROPERTY_VALUE ------------------------------------------------------------ ult_temp_tablespace TEMP DEFAULT_PERMANENT_TABLESPACE USERS DEFAULT_EDITION ORA $ BASE DEFAULT_TBS_TYPE smallfile SQL> select tablespace_name from dba_tablespaces where tablespace_name = 'temp '; TABLESPACE_NAME ---------------------------- temp SQL> 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, add the database to/etc/oratab, configure the listener, and other SQL> create spfile from pfile; 3. Summary a. Cloning a user-managed 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, the Oracle database instance starts and closes the process c, pay attention to understanding the role of several different files, namely: the Oracle parameter file, the Oracle password file, the Oracle control file, and the final opened database file d are equivalent to the restoration operation after the hot standby database is copied to the target database directory, that is, it is equivalent to rman's restore operation e. database recovery operations use the using backup controlfile method, because the control file and data file are not . For more information, see using backup controlfile f. Because the archived logs are located in the archive location of the original database, therefore, during recovery, the set logsource clause is used to specify the location g where the archived log is located and when the control file is created, because it is a new db, you must use the resetlog method; otherwise, the ORA-01223 is received: RESETLOGS must be specified to set a new database name