CORE_DUMP_DEST Make sure that the files have the correct permissions and ownership. MAXLOGMEMBERS 5 Copy SPFILE: Our first goal should be to put clone database into nomount stage. With the Partitioning, OLAP and Data Mining options, 3) Make the necessary changes in pfile of CLONE database as shown below. Save my name, email, and website in this browser for the next time I comment. This file will contain steps and as well as CREATE CONTROLFILE statement. Now controlfile created. select member from v$logfile; Change). MAXDATAFILES 100 Run the following to reset it: SQL> alter database rename global_name to . steps: LOG_ARCHIVE_DEST_1, 7. (LogOut/ Get the file path information using below query. Here we have use SET DATABASE, Because we are cloning the database in target with a new db name. Book a mock interview and get instant feedback -, Hot database cloning is more suitable for databases which are running, Open the /tmp/prod_ctl.sql file and only keep, '/u01/app/oracle/oradata/prod/redo01.log', '/u01/app/oracle/oradata/prod/redo02.log', '/u01/app/oracle/oradata/prod/redo03.log', '/u01/app/oracle/oradata/prod/system01.dbf', '/u01/app/oracle/oradata/prod/sysaux01.dbf', '/u01/app/oracle/oradata/prod/undotbs01.dbf', '/u01/app/oracle/oradata/prod/users01.dbf'. Cold database backup means while taking backup or doing cloning, we need to shutdown the source database. CONTROL FILES Change the word REUSE to set and the olddbname to newdbname. a slower development machine you might want to consider reducing some values. And Place the clone_DB(new DB) pfile on /$ORACLE_HOME/dbs. MAXINSTANCES 1 Oracle cold cloning is one the reliable methods that is done using the cold database backups. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf using os command to copy the datafile. 6.Taken the controlfile backup Alter tablespace system end backup; 5.Repeat the steps 1-3 for all your tablespaces. Using backup controlfile trace to generate the create controlfile scripts. Now open the parameter file in clone database and change the following parameters with the respective current location. If RMAN isnt going to be used, there is no harm in changing the ID anyway and its a good practice to do so. ORA-00200: controlfile could not be created, ORA-00202: controlfile: /u03/oradata/dg9a/control01.ctl, Double check the pfile created in step 2. 3.Create the password file using orapwd utility. The following query will display all datafiles, tempfiles and redo logs: Make sure that the clone databases file-system is large enough and has all necessary directories. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); Enter your email address to subscribe to this blog and receive notifications of new posts by email. Run the following to reset it: If RMAN is going to be used to back-up the database, the database ID must be changed. Also change the datafiles location parameter to clone database location. USER_DUMP_DEST Software in Silicon (Sample Code & Resources). The drawback of this method is that the database must be shutdown while taking the cold backup. And Place the BST4 pfile on /$ORACLE_HOME/dbs. Let us check the data files and redo log files on source (prod) database, Create directory locations for data files, redo logs and temp files on target (dev) server, Shutdown the prod server and start copying the data files, redolog files and the temp file from source to target server, Start Clone Server: We have everything to start the clone database on target (dev) server. It is advisable to perform a few checks at this point: The new database will still have the source databases global name. If the cloned database is to have a new name, this will need to be changed, as will any paths. (bdump,udump,create,pfile,cdump,oradata), 2.Change the init.ora parameter like control file path, dbname, instance name etc. Here are a couple of common errors and solutions: ORA-01113: file 1 needs media recoveryYou probably forgot to stop the source database before copying the files.

Once created, the new pfile will need to be edited. It is advisable to perform a few checks at this point: SQL> select distinct status from v$datafile; The new database will still have the source databases global name. In step 1 , We had taken backup of the controlfile as trace, We will use that sql file to re-create the controlfile. select name from v$controlfile; Create pfile=/u01/backup/inittroy.ora from spfile; If database running in pfile using os command to copy the pfile and placed in backup path. Run create controlfile script to create the controlfile. This site uses Akismet to reduce spam. a slower development machine you might want to consider reducing some values. Respond with Y. SQL*Plus: Release 10.2.0.1.0 Production on Fri Jul 5 19:38:02 2013. Pay particular attention to the control locations. (LogOut/ In original database generate CREATECONTROLFILE statement by typing the following command. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); This site uses Akismet to reduce spam. Change). Copy Control Files: To take the clone database from nomount to mount stage, we need to copy control files from source to target server. Your email address will not be published. 1. MAXLOGHISTORY 453 CREATE CONTROLFILE SET DATABASE clone RESETLOGS FORCE LOGGING NOARCHIVELOG (LogOut/ The following query will display all datafiles, tempfiles and redo logs: Make sure that the clone databases file-system is large enough and has all necessary directories. Change), You are commenting using your Facebook account. Select tablespace_name, file_name from dba_data_files order by 1; 3.Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path. Currently all cloning is performed via Oracle RMAN. Now copy the pfile from the original database server to the clone database server and placed it under the $ORACLE_HOME/dbs location. GROUP 1 /U01/oradata/clone/redo01.log SIZE 200M, Cloning Oracle Database Cold Cloning, Hot Cloning, Clone an Oracle Database using Cold Physical Backup, 1. If you are cloning a production database onto. In clone database SERVER export ORACLE_SID environment variable and start the instance. (LogOut/ Note: Check the logfile, datafiles & instance status, **********************************************************************************, Clone an Oracle Database using Hot Physical Backup It is quite common to run into problems at this stage. SQL> startup nomount pfile=initNEWDB_NAME.ora; 8. Review the contents of the file and make, Also think about adjusting memory parameters. BACKGROUND_DUMP_DEST Cloning can be done on separate hosts or on the same host and is different from standby database. On source database, take controlfile trace backup under /tmp/prod_ctl.sql file. If you wish to keep the database same as source, then you can use the command REUSE DATABASE command. But Oracle suggests do not clone the database on same host with same database name. Here we will clone a database SRCDB to a new server with name TRGDB. 4. This will create a new pfile in the $ORACLE_HOME/dbs directory. Delete any other lines that you may see.

For this, we need to copy the spfile from prod server to dev server, Create pfile from spfile and open the parameter file. If you are cloning a production database onto. Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. Recover database using backup controlfile until cancel; Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. Now open the parameter file in clone database and change the following parameters with the respective current location. 4) Fire the following command to backup PROD database controlfile to trace file. Drop that tablepspace before the cold backup. Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. Create a control file for the new database. Copy, scp or ftp the files from the source database/machine to the target. (Refer below example) Copy the CREATE CONTROLFILE statement and paste it in a notepad. If you are not, just copy the existing pfile. If the last step went smoothly, the database should be open. Upgrade database from 11g to 12c manually, How to run SQL tuning advisor for a sql_id, Upgrade database from 12.1.0.2 to 12.2.0.1, ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT, Transparent Data Encryption (TDE) in oracle 12c, How to drop and recreate temp tablespace in oracle, Prerequisite check CheckActiveFilesAndExecutables failed, Steps to Apply PSU patch on oracle 11g database. Select * from v$backup; 6.Recover the database using controlfile. Also change the datafiles location parameter to BST4 database location. (LogOut/ Change), You are commenting using your Twitter account. Sometimes DBAs need to clone databases to test backup, development environments and Relocating an database to another machine, and recovery strategies or export a table that was dropped from the production database and import it back into the production database. Create the necessary directory on the clone database (destination database) server on your desired location. 2.Alter tablespace system begin backup; To ensure the begin backup mode tablespace using below query DBACLASS.COM is a knowledgebase for Oracle Database administrators. If the control_file setting is ok, make sure that the control. Copyright (c) 1982, 2005, Oracle. 1.Get the file path information using below query. With the source database started, identify all of the databases files. GROUP 2 /U01/oradata/clone/redo02.log SIZE 200M, Save and close the file, Create the logfile and datafiles locations on clone server. SQL> create pfile=init.ora from spfile; This will create a new pfile in the $ORACLE_HOME/dbs directory. Change the name of the text file as control.sql. (LogOut/ To do this, connect to the source database and request a dump of the current control file. For hot database cloning, database has to be in archivelog mode and there no need to shutdown the database. Datafiles cloning: Note down the oldest log sequence number on the source database, Place the source database into to backup mode, Copy all datafiles and logfiles from database to target server, Release the source database from backup mode, Switch the current log file and note down the oldest log sequence number. DATAFILE SOURCE DB SRCDBTARGET DB TRGDBif(typeof ez_ad_units!='undefined'){ez_ad_units.push([[728,90],'dbaclass_com-medrectangle-3','ezslot_1',105,'0','0'])};if(typeof __ez_fad_position!='undefined'){__ez_fad_position('div-gpt-ad-dbaclass_com-medrectangle-3-0')}; We can copy the pfile from source db and the change the required parameters like DB_NAME and control_file,audit_file_dest,diag location.if(typeof ez_ad_units!='undefined'){ez_ad_units.push([[300,250],'dbaclass_com-medrectangle-4','ezslot_4',108,'0','0'])};if(typeof __ez_fad_position!='undefined'){__ez_fad_position('div-gpt-ad-dbaclass_com-medrectangle-4-0')}; This is an important steps in cloning process. Make sure the control_files setting, is pointing at the correct location. After deleting all the other lines, the file must look like below. Change REUSE to SET and NORESETLOGS to RESETLOGS, Copy the /tmp/prod_ctl.sql file from prod to dev server, Copy Data Files & Redo Log Files: It's time to copy data files and redo log files from source to target server. Change), You are commenting using your Facebook account. Make sure the control_files setting, is pointing at the correct location. CREATE CONTROLFILE SET DATABASE SRCDB RESETLOGS FORCE LOGGING ARCHIVELOG. Cloning is a process that can be used to create a copy of the existing Oracle database. Change the Database name & files path, also change REUSE needs to be changed to SET. 3. If RMAN is going to be used to back-up the database, the database ID must be changed. 3. It is quite common to run into problems at this stage. /U01/oradata/clone/system01.dbf, 3.Taken the control file backup Change), You are commenting using your Twitter account. (LogOut/ Recover clone database: Let us create the controlfiles for the clone database and put it in mount mode, Recover the clone database using backup controlfile option, You will be prompted to feed the archive log files henceforth. If you are not, just copy the existing pfile. NID will ask if you want to change the ID. Once created, the new pfile will need to be edited. /U01/oradata/clone/users01.dbf, All rights reserved. ORA-31626: job does not exist and How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS: Oracle DBA Interview Questions and Answers, Oracle RMAN Backup and Recovery in CDB & PDBs, ORA-04031,ORA-06508,RMAN-00554,RMAN-06429. Do not copy the control files across. NID will ask if you want to change the ID. Open the control.sql file and make the necessary changes, 6) Make necessary directories for clone database, 7) Copy all the datafiles and redolog files from PROD database directory to CLONE database directory, 8) Fire following command to connect to CLONE database, SQL*Plus: Release 10.2.0.1.0 Production on Fri Jul 5 20:38:58 2013, 9) Run the control.sql script on clone database, 10) Fire the following command to open the database. In clone database SERVER export ORACLE_SID environment variable and start the instance. Pay particular attention to the control locations. LOGFILE files were not copied with the rest of the database files. With the source database started, identify all of the databases files. (Database in windows we need to create the service id using oradim utility). Book a mock interview and get instant feedback - Learn More. Respond with Y. (LogOut/ Note. 5.Create the control file for cloning database. Note. Required fields are marked *. 1.Create the appropriate folder in corresponding path & placed the backup files in corresponding folder. If the source database has a complex file structure, you might want to consider modifying the. To ensure the begin backup mode tablespace using below query Our CREATE CONTROLFILE script is ready, we will use it later in the cloning process. If the control_file setting is ok, make sure that the control. In my case, all files reside under one location. Connected to: If the cloned database is to have a new name, this will need to be changed, as will any paths. SQL> startup nomount pfile=initBST4.ora; 6. parameter datapump Hit enter, enter until all the archive logs are applied and then type CANCEL. You can get this tempfile script in the end controlfile script .if(typeof ez_ad_units!='undefined'){ez_ad_units.push([[728,90],'dbaclass_com-banner-1','ezslot_11',111,'0','0'])};if(typeof __ez_fad_position!='undefined'){__ez_fad_position('div-gpt-ad-dbaclass_com-banner-1-0')}; Replace the tempfile location as per target db and execute in target db: Your email address will not be published. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); Cloning Oracle Database Cold Cloning, HotCloning. Alter database backup controlfile to trace as /u01/backup/control01.ora; 5.Copy all the data files/log files using os command & placed in backup path. This step assumes that you are using a spfile. Source and target files locations is same, Parameter file cloning: Find out the names of datafiles on the source database, Create pfile from spfile and copy it to target server, On the target server, open the pfile and create locations to hold controlfiles and dump files, In my case I had to create below two main locations, Controlfile cloning: On the target server, take controlfile backup to trace location, On target server, open the trace file and follow below steps, Delete everything before "CREATE CONTROLFILE", And you must be left with below. Now, go to the USER_DUMP_DEST directory on the original Database server and open the latest trace file.The trace file will have the form ora_NNNN.trc with NNNN being a number. Once it has finished, start the database up again in sqlplus: Add entries for new database in the listener.ora and tnsnames.ora as necessary. If they were, delete or rename them. Open the /tmp/prod_ctl.sql file and only keep CREATE CONTROLFILE statement. ORA-01113: file 1 needs media recoveryYou probably forgot to stop the source database before copying the files. From sqlplus: alter database backup controlfile to trace as /home/oracle/cr_.sql, FROM: CREATE CONTROLFILE REUSE DATABASE BST2 RESETLOGS , TO: CREATE CONTROLFILE set DATABASE BST4 RESETLOGS . 6. This method is usually used for test database when database is in noarchivelog mode. Once it has finished, start the database up again in sqlplus: Add entries for new database in the listener.ora and tnsnames.ora as necessary. If they were, delete or rename them. CHARACTER SET WE8ISO8859P1; Note: placed the script in sql prompt. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Production Learn how your comment data is processed. Example: Select name from v$datafile; Now open the parameter file in clone database and change the following parameters with the respective current location. clone database using cold backup in oracle, How to solve High ITL Waits for given segments. /U01/oradata/clone/undotbs01.dbf, FROM: CREATE CONTROLFILE REUSE DATABASE olddbname RESETLOGS , TO: CREATE CONTROLFILE set DATABASE newdbname RESETLOGS . (LogOut/ Database Name: troy. Copy all archive log files generated on the source database during begin backup and the end backup mode to the target server. If the source database has a complex file structure, you might want to consider modifying the. Change), You are commenting using your Twitter account. If the last step went smoothly, the database should be open. Alter database backup controlfile to trace as /u01/backup/control01.ora; 7.Backup all your archive log files between the previous backup and the new backup as well. Learn how your comment data is processed. My experience, findings and thoughts in my daily work with Oracle products.. Drop and Recreate Online Redolog File inoracle, steps for cloning oracle database using cold backup. Make sure that the files have the correct permissions and ownership.

An investment in KNOWLEDGE pays the best interest! Review the contents of the file and make, Also think about adjusting memory parameters. This step assumes that you are using a spfile. If you just bother, take pain to answer my questions, (you are free to ignore them; assuming that all these non-sense) just provide as much as detail as you can, then i am sure we may resolve your issue (may be i will fail to help you) but i think above required answer will help other member to give you a right direction; i know you are in hurry but just cool down, try to answer my question and wait right ? After firing the above command open the latest trace file generated in udump folder.Copy the following content and paste it in a text file. Select * from v$backup; (refer the Change#, Time column). If RMAN isnt going to be used, there is no harm in changing the ID anyway and its a good practice to do so. Hot and cold database cloning methods are outdated and these methods of cloning are only for good to know purpose. Note- Both the source and target db server should be on same platform and the target db version will be that of the source db.So make sure oracle binary is already installed on target db server. Do not copy the control files across. Create a free website or blog at WordPress.com. SNIPPET FROM controlfile sql script:if(typeof ez_ad_units!='undefined'){ez_ad_units.push([[728,90],'dbaclass_com-box-4','ezslot_5',110,'0','0'])};if(typeof __ez_fad_position!='undefined'){__ez_fad_position('div-gpt-ad-dbaclass_com-box-4-0')}; Lets modify this sql by replacing the datafile location of source with that of target and the new db name as TRGDB. 4.Once copied the datafile, release the tablespace from begin backup mode to end backup Change the word REUSE to set and the BST2 to BST4. 5. I am doing cloning of a database from cold backup for testing, actully one datafile has been croupted, so my issue is that, can i exclude tht datafile from controlfile when creating the controlfile and is that poosible that controlfile will created sucessfully and database will open sucessfully. GROUP 3 /U01/oradata/clone/redo03.log SIZE 200M SQL>alter database backup controlfile to trace; This will create a trace file containing the CREATE CONTROLFILE command to recreate the controlfile in text form. Let us create audit_file_dest, control_files (only directory path), db_recovery_file_dest and diagnostic_dest on target (dev) server. Change). ----------------------------------------------------------------, ------------------------------------------------------------------, -------------------------------------------------------------------, CREATE CONTROLFILE SET DATABASE "dev" RESETLOGS NOARCHIVELOG, ORA-01130: database file version 11.2.0.4.0 incompatible with ORACLE version, Cloning with Cold Backup (offline/closed). MAXLOGFILES 50 Run create controlfile script to create the controlfile. And Place the BST4 pfile on /DV1_u31/oraBST2/db/tech_st/10.2.0/dbs, 5. Change), You are commenting using your Facebook account. Hot database cloning is more suitable for databases which are running 24/7 type of databases and is done using the hot backup. Example :- mkdir udump adump cdump bdump arch.

Copy, scp or ftp the files from the source database/machine to the target. Use tab to navigate through the menu items. files were not copied with the rest of the database files. Creating Clone database with different name using coldbackup, DB Vault: Datapump error ORA-39127 ORA-01031, Spectre and Meltdown impact on Oracle DB performance. Here are a couple of common errors and solutions: ORA-00200: controlfile could not be created, ORA-00202: controlfile: /u03/oradata/dg9a/control01.ctl, ORA-27038: skgfrcre: file exists, Double check the pfile created in step 2. 4.