Manually Clone an experiment with a different name database

Building Database Clone objects is a common daily O & M requirement. During actual development and testing, you often need to quickly copy a database environment containing business data. Previously, we have introduced how to use the RMAN Duplicate function. This article discusses how to use the traditional SQL Plus tool to create a clone database.
 

Recommended reading:

RMAN: Configure an archive log deletion policy

Basic Oracle tutorial-copying a database through RMAN

Reference for RMAN backup policy formulation

RMAN backup learning notes

Oracle Database Backup encryption RMAN Encryption

 

1. Environment Introduction and experiment Objectives

We chose Oracle 11gR2 for testing. The operating system is Linux 5.3.

SQL> select * from v $ version;

 

BANNER

----------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

 

The database instance name is ora11g. The related environment variables are as follows:

 

[Oracle @ SimpleLinux ~] $ Env | grep ORA

ORACLE_SID = ora11g

ORACLE_BASE =/u01/app

ORACLE_HOME =/u01/app/oracle

 

Database parameter files, control files, and data files comply with the OFA specification. The file name policy is OMF.

The objective of this experiment is to create the database ora11cl with the same content as ora11g (clone ). However, different database names are required. For environmental reasons, I use the same host for file replication. If the database is cloned from different hosts, the operations are identical. Note: It is recommended that the Oracle software version or patch be identical for cloning databases in different regions. This prevents version exceptions.
 
 

2. Principle Analysis and preparation

The Oracle database runs three major files: data files, control files, and log files (online + archived ). Database Replication is required: The parameter file starts a brand new instance and uses a new instance name; the control file is rebuilt and logs and data files are identified; if not completely closed, you must also consider completing the application for archiving logs.
 
In general, if the database is completely closed, we can avoid the apply process of archiving logs. You can use pfile to create a new database instance. After entering nomount, you can try to create a new control file to modify the information of the replacement file header.
 
Determine the directory of the current control file:

SQL> select name from v $ controlfile;

NAME

--------------------------------------------------------------------------------
 
/U01/app/oradata/ORA11G/controlfile/o1_mf_92t72zkf _. ctl

/U01/app/fast_recovery_area/ORA11G/controlfile/o1_mf_92t72zyj _. ctl

 

If the new database still complies with the OFA policy, the corresponding ORA11CL directory needs to be created, including the data directory and log directory. Prepare the cp Command Script.

 

 

SQL> select 'cp' | file_name | ''| replace (file_name, 'ora11g', 'ora11cl ') from dba_data_files;
 
Cp/u01/app/oradata/ORA11G/datafile/o1_mf_users_92t6zl83 _. dbf/u01/app/oradata/ORA11CL/datafile/o1_mf_users_92t6zl83 _. dbf
 
Cp/u01/app/oradata/ORA11G/datafile/o1_mf_undotbs1_92t6zl6d _. dbf/u01/app/oradata/ORA11CL/datafile/logs _. dbf
 
Cp/u01/app/oradata/ORA11G/datafile/o1_mf_sysaux_92t6zl5k _. dbf/u01/app/oradata/ORA11CL/datafile/logs _. dbf
 
Cp/u01/app/oradata/ORA11G/datafile/o1_mf_system_92t6zl2m _. dbf/u01/app/oradata/ORA11CL/datafile/o1_mf_system_92t6zl2m _. dbf
 
Cp/u01/app/oradata/ORA11G/datafile/o1_mf_example_92t74b1f _. dbf/u01/app/oradata/ORA11CL/datafile/logs _. dbf
 
Cp/u01/app/oradata/ORA11G/datafile/o1_mf_trcatbl_96mlzz0j _. dbf/u01/app/oradata/ORA11CL/datafile/logs _. dbf
 
Cp/u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_9j2sxn9r _. dbf/u01/app/oradata/ORA11CL/datafile/logs _. dbf
 
Cp/u01/app/oradata/ORA11G/datafile/o1_mf_awp_9khloyod _. dbf/u01/app/oradata/ORA11CLO/datafile/o1_mf_awp_9khloyod _. dbf
 
 

8 rows selected

 

There are many methods to back up the control file dump, because this experiment wants to use the Create control file policy, so it backs up to the flat text file.

 

SQL> set heading on;

SQL> select value from v $ diag_info where name = 'default Trace file ';

VALUE

----------------------------------------------

/U01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3577.trc

 

SQL> alter database backup controlfile to trace;

Database altered

 

Open the trace file, locate the script location, and select resetlogs mode to create it.

 

 

STARTUP NOMOUNT

Create controlfile reuse database "ORA11G" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

# MAXLOGHISTORY 1248

LOGFILE

GROUP 1 (

'/U01/app/oradata/ORA11G/onlinelog/o1_mf_1_94hvy8pw _. log ',

'/U01/app/fast_recovery_area/ORA11G/onlinelog/ow.mf_1_94hvy9kk _. Log'

) SIZE 50 m blocksize 512,

GROUP 2 (

'/U01/app/oradata/ORA11G/onlinelog/o1_mf_2_94hvyc26 _. log ',

'/U01/app/fast_recovery_area/ORA11G/onlinelog/o1_mf_2_94hvydb6 _. Log'

) SIZE 50 m blocksize 512,

GROUP 3 (

'/U01/app/oradata/ORA11G/onlinelog/o1_mf_3_94hvyfvx _. log ',

'/U01/app/fast_recovery_area/ORA11G/onlinelog/o1_mf_3_94hvyh9v _. Log'

) SIZE 50 m blocksize 512

-- STANDBY LOGFILE

DATAFILE

'/U01/app/oradata/ORA11G/datafile/o1_mf_system_92t6zl2m _. dbf ',

'/U01/app/oradata/ORA11G/datafile/o1_mf_sysaux_92t6zl5k _. dbf ',

'/U01/app/oradata/ORA11G/datafile/o1_mf_undotbs1_92t6zl6d _. dbf ',

'/U01/app/oradata/ORA11G/datafile/o1_mf_users_92t6zl83 _. dbf ',

'/U01/app/oradata/ORA11G/datafile/o1_mf_example_92t74b1f _. dbf ',

'/U01/app/oradata/ORA11G/datafile/o1_mf_trcatbl_96mlzz0j _. dbf ',

'/U01/app/oradata/ORA11G/datafile/o1_mf_testtbl_9j2sxn9r _. dbf ',

'/U01/app/oradata/ORA11G/datafile/o1_mf_awp_9khloyod _. dbf'

Character set AL32UTF8

;

 

You need to modify the online group information in two places. Because we completely shut down the database, there will be no steps to restore the instance after it is started. In addition, some files in the OMF policy cannot be specified in some versions, so to avoid the trouble, delete the log file information and re-create logs during Oracle execution. Another change is the directory information of the data file. Replace ORA11G with ORA11CL.
 
 

 

STARTUP NOMOUNT

Create controlfile set database "ORA11CL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

# MAXLOGHISTORY 1248

LOGFILE

GROUP 1 SIZE 50 m blocksize 512,

GROUP 2 SIZE 50 m blocksize 512,

GROUP 3 SIZE 50 m blocksize 512

-- STANDBY LOGFILE

DATAFILE

'/U01/app/oradata/ORA11CL/datafile/o1_mf_system_92t6zl2m _. dbf ',

'/U01/app/oradata/ORA11CL/datafile/o1_mf_sysaux_92t6zl5k _. dbf ',

'/U01/app/oradata/ORA11CL/datafile/o1_mf_undotbs1_92t6zl6d _. dbf ',

'/U01/app/oradata/ORA11CL/datafile/o1_mf_users_92t6zl83 _. dbf ',

'/U01/app/oradata/ORA11CL/datafile/o1_mf_example_92t74b1f _. dbf ',

'/U01/app/oradata/ORA11CL/datafile/o1_mf_trcatbl_96mlzz0j _. dbf ',

'/U01/app/oradata/ORA11CL/datafile/o1_mf_testtbl_9j2sxn9r _. dbf ',

'/U01/app/oradata/ORA11CL/datafile/o1_mf_awp_9khloyod _. dbf'

Character set AL32UTF8

;

 

In addition, note that there is no temp tablespace information in the script. In the trace file, we can see that Oracle can create a tablespace after open reset. Of course, the original Trace file contains the name information of the temporary file, and the author also deletes it, prompting Oracle to automatically create it.
 
 

Alter database open resetlogs;

 

Alter tablespace temp add tempfile size 481296384 autoextend on next 655360 MAXSIZE 32767 M;
 
Alter tablespace temptest add tempfile size 1024 m autoextend off;

Alter tablespace temptest add tempfile size 525328384 autoextend off;

Alter tablespace AWP_TEMP add tempfile size 1024 m autoextend on next 104857600 MAXSIZE 32767 M;
 
 

Finally, prepare the parameter file. You can use ora11g as the template for rewriting.

 

SQL> create pfile from spfile;

File created.

 

[Oracle @ SimpleLinux dbs] $ ls-l | grep init

-Rw-r -- 1 oracle oinstall 2851 May 15 2009 init. ora

-Rw-r -- 1 oracle oinstall 1070 Mar 10 initora11g. ora

 

Modify the content, mainly the database name. The control file parameter control_files must be blocked. All directories related to ora11g need to be rewritten.

 

Ora11cl. _ db_cache_size = 71303168

Ora11cl. _ java_pool_size = 20971520

Ora11cl. _ large_pool_size = 4194304

Ora11cl. _ oracle_base = '/u01/app' # ORACLE_BASE set from environment

Ora11cl. _ pga_aggregate_target = 113246208

Ora11cl. _ sga_target = 264241152

Ora11cl. _ shared_io_pool_size = 0

Ora11cl. _ shared_pool_size = 150994944

Ora11cl. _ streams_pool_size = 8388608

*. Audit_file_dest = '/u01/app/admin/ora11cl/adump'

*. Audit_trail = 'db'

*. Compatible = '11. 2.0.0.0'

# *. Control_files = '/u01/app/oradata/ORA11CL/controlfile/o1_mf_92t72zkf _. ctl', '/u01/app/fast_recovery_area/ORA11CL/controlfile/logs _. ctl'
 
*. Db_block_size = 8192

*. Db_create_file_dest = '/u01/app/oradata'

*. Db_domain =''

*. Db_name = 'ora11cl'

*. Db_recovery_file_dest = '/u01/app/fast_recovery_area'

*. Db_recovery_file_dest_size = 10737418240

*. Diagnostic_dest = '/u01/app'

*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = ora11cloneXDB )'

*. Log_checkpoints_to_alert = TRUE

*. Memory_max_target = 360 m

*. Memory_target = 360 m

*. Open_cursors = 300

*. Processses = 150

*. Remote_login_passwordfile = 'clusive'

*. Undo_retention = 300

*. Undo_tablespace = 'undotbs1'

*. Use_large_pages = 'true'

 

Save as the initora11clone. ora file.

 

[Oracle @ SimpleLinux dbs] $ ls-l | grep init

-Rw-r -- 1 oracle oinstall 2851 May 15 2009 init. ora

-Rw-r -- 1 oracle oinstall 1127 Mar 10 initora11clone. ora

-Rw-r -- 1 oracle oinstall 1070 Mar 10 initora11g. ora

 

Create a directory. manually create a file system directory based on the previously located directory information.

 

[Root @ SimpleLinux ~] # Mkdir-p/u01/app/admin/ora11cl/adump

[Root @ SimpleLinux ~] # Mkdir-p/u01/app/fast_recovery_area/ORA11CL/controlfile/

[Root @ SimpleLinux ~] # Mkdir-p/u01/app/oradata/ORA11CL/datafile

[Root @ SimpleLinux ~] # Mkdir-p/u01/app/oradata/ORA11CL/onlinelog/

[Root @ SimpleLinux ~] # Mkdir-p/u01/app/fast_recovery_area/ORA11CL/onlinelog/

[Root @ SimpleLinux ~] # Cd/u01

[Root @ SimpleLinux u01] # chown-R oracle: oinstall/u01

 

For more details, please continue to read the highlights on the next page:

1 2 Next Page