By signing up, you agree to our Terms of Use and Privacy Policy. Edit oratab file and put database back in MONITOR. Open the database with resetlogs option. 9. (ADDRESS_LIST = Listed below are the most typical uses: 1. Optional: In the REDO log size box, type or select the minimum permitted size (in MB) for the redo log file. >, Disaster Recovery and Replication 7. Start the clone database in NOMOUNT stage. On SunOS : /var/opt/oracle/oratab On HP-UX : /etc/oratab, set oracle environment variables ORACLE_SID ORACLE_HOME or Use (. 4.Once copied the datafile, release the tablespace from begin backup mode to end backup 6. But this is cumbersome if you have to do the data refresh frequently and import takes a long time if your database is of good size. 3. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy, Explore 1000+ varieties of Mock tests View more, Special Offer - Oracle Course Training Learn More, Oracle Training (14 Courses, 8+ Projects), 14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access, Oracle DBA Database Management System Training (2 Courses), All in One Financial Analyst Bundle - 250+ Courses, 40+ Projects, Oracle Apps Technical Interview Questions. If the source is development/test database, confirm the downtime available for the source database. If you have access to the destination client Oracle database, then the Oracle home path is populated automatically. Backup-Based Duplication Steps: RMAN supports two basic types of duplication: In active database duplication, RMAN connects as TARGET to the source database instance and as AUXILIARY to the auxiliary instance. (SID_NAME =PROD) Notes: The Oracle orapwd command line utility assists the DBA with granting SYSDBA and SYSOPER privileges to other users. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); Check the target database space is sufficient or not. 6. CHARACTER SET WE8ISO8859P1; Note: placed the script in sql prompt. cd /u02/PRODCLON/ Keep the Database in Archive log or No archive log mode as per the backup Policy. We can send you a link when your PDF is ready to download. DBA performs Oracle database cloning to test backup and recovery strategies. Change). 12. MAXDATAFILES 100 1.Get the file path information using below query. Once the control files successfully created, open the database with resetlogs option. Take an export backup of target if required. -datafile -init.ora -redolog files -controlfile..trc. Create the control file for the clone database using the trace control file. Administrators do not need to connect to the source database while performing database cloning and that reduces the chances of impact on the production system. The default value is 50 MB. . If the Source is -Hotbackup-Production Database, Edit oratab file and put database in DONTMONITOR mode. Why to use RMAN over traditional OS duplicate command? . Specify the absolute path and file name for the archive log files and keep feeding them until you cross the LAST old sequence no. The method used here is actually a backup/recovery of the production database on the test server. ) select member from v$logfile; 13. . Optional: Create an additional instance on the destination server with the same name as the clone database so that you can store the clone instance properties (for example, ORACLE_HOME). Restoring a Database to Its Current Location (In Place), Restoring a Database to a Different Location (Out of Place), Database Application Migration to the Cloud export ORACLE_SID=PROD user_dump_dest=/u02/PRODCLON/udump MAXINSTANCES 8 The answer is that, if you copy a database with operating system utilities instead of the DUPLICATE command, then the DBID (an internal, uniquely generated number that differentiates databases) of the copied database remains the same as the original database but the DUPLICATE commandautomatically assigns the duplicate database a different DBID so that it can be registered in the same recovery catalog as the source database. Copy the export dump file to Targer server. Copy the following files to Targer server. (LogOut/ To ensure the begin backup mode tablespace using below query (9) And then finally duplicate the database as: DUPLICATE DATABASE TO kkdb */ kkdb is new db name You can use the cloning feature to do the following: Duplicate large databases in a short amount of time, Retrieve data quickly without without intensive resource use on the production environment. how to stop hacking in your database as an oracle DBA???? You may also have a look at the following articles to learn more , All in One Data Science Bundle (360+ Courses, 50+ projects). MAXDATAFILES 100 (GLOBAL_DBNAME = PRODCLON) datafiles redolog files create_db.sql init.ora. db_file_name_convert=(/u01/PROD/oradata,/u02/PRODCLON/oradata), This parameter specifies from where to where the datafiles should be cloned, log_file_name_convert=(/u01/PROD/oradata,/u02/PRODCLON/oradata), This parameter specifies from where to where the redologfiles should be cloned. Recover database using backup controlfile until cancel; Note: Check the logfile, datafiles status. There is a DUPLICATE command that is provided by RMAN and that is used for backup of the database to create the clone database.

The Oracle Agent and the MediaAgent must be installed on the proxy.

Optional: In the PGA +SGA size box, type or select the memory size (in MB) that you want to allocate for the Post Global Area (PGA) and System Global Area (SGA). Drop the required user (Target schema) using Drop user cascade; Re-create the required user in the target (development) database using Create < username > identified by < password >, default tablespace < users default tablespace name > temporary tablespace < temporary tablespace name >, Export the required schema using exp / owner= file=. background_dump_dest=/u02/PRODCLON/bdump :wq! . . Oracle cloning is often the fastest way to make a copy of an existing Oracle database. (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1/) As a rule, testing and development should not be done on your production database. RMAN> duplicate target database to PRODCLON; Note:The preceding command restores all files from the backup of the target database to the clone database destination using all available archive log files. export ORACLE_SID=PRODCLON 2022 - EDUCBA. It is used for relocating an Oracle database to another machine. 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. Optional: In the Staging path box, type the full path to the location that the Oracle software uses to stage the clone. . Alter tablespace system end backup; In every oracle development and production environment there will become the need to transport the entire database from one physical machine to another. /u02/PRODCLON/oradata/users01.dbf, 8. Copy create_db.sql to /oracle/admin/dbrefresh/ on Target Server. 9. (1) Create a backup of the source database by setting auto backup ON, by default CONTROLFILE AUTOBACKUP is OFF. background_dump_dest=/u02/PRODCLON/bdump A faster alternative is to clone the database. You cannot clone an Oracle 12c container database or pluggable database. /u02/PRODCLON/oradata/example01.dbf /u02/PRODCLON/oradata/undotbs01.dbf, If the Oracle database version is 11.2.0.3.0, 11.2.0.3.1, or 11.2.0.3.2, install Oracle patch 13366202 or the latest Oracle 11gR2 Patch Set Update on the destination database. Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. From the navigation pane, go to Protect > Databases. Change the Database name & files path, also change REUSE needs to be changed to SET. That copy of the database can be used for development. Do it only when required, Start the instance in NOMOUNT mode. Validate the export log. (4) Create pfile on source and get that file copied to target. 2.Alter tablespace system begin backup; To ensure the begin backup mode tablespace using below query cp /u01/PROD/source/oradata/*.dbf /u02/PRODCLON/oradata/. 6. Edit the clone database parameter file, cd /u02/PRODCLON (ORACLE_HOME = /u02/oracle/product/10.2.0/db_1/) https://dbaprakash.wordpress.com/2015/03/04/database-cloning-steps/. Oracle Cloning is a very useful procedure to take the existing database back up. (bdump,udump,create,pfile,cdump,oradata), 2.Change the init.ora parameter like control file path, dbname, instance name etc. Import the dump file of source (production) database into target (development) database using -imp / fromuser=touser= file=.

Do Alter system switch logfile n+1 number of times, where n is number of archive groups. CHARACTER SET AL32UTF8, 11. If you want to change the location of the temp files, create a custom pfile and add the new path to the db_create_file_dest parameter in the pfile. DBNAME, BDUMP,CDUMP, DUMP & CONTROL_FILE. (5) Shuts down and restarts the database instance in NOMOUNT mode. 12. Find the path and names of datafiles, control files, and redo log files. Select * from v$backup; 6.Recover the database using controlfile. control_files=/u02/PRODCLON/oradata/cntrl01.ctl Startup the database in NOMOUNT stage and exit. rman target / auxiliary sys/sys@con_PRODCLON. .

Expand the Destination options node, and then provide the following details: Optional: In the PFile box, type the full path to the destination startup parameter file. Have the backup administrator configure a client and a subclient for the clone feature. /u02/PRODCLON/oradata/example01.dbf control_files=/u02/PRODCLON/oradata/cntrl01.ctl Switch the current log file and note down the oldest log sequence number. :wq! Now, the developers need up-to-date data for testing purposes, and it is the DBA who provides this to them. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS. user_dump_dest=/u02/PRODCLON/udump 7. Install the Oracle Agent and the MediaAgent on the proxy. Copy create_db.sql to /oracle/admin/dbrefresh/ on Target Server. . The Oracle user in both the source and destination computers must have the same user ID. On SunOS : /var/opt/oracle/oratab On HP-UX : /etc/oratab, set oracle environment variables ORACLE_SID ORACLE_HOME or Use (. Create the control file by running trace file from the trace path. 3.Taken the control file backup /U01/oradata/clone/undotbs01.dbf, set oracle environment variables ORACLE_SID ORACLE_HOME or Use (. /u02/PRODCLON/oradata/system01.dbf, GROUP 1 /u02/PRODCLON/oradata/redo01.log SIZE 5M, End the begin backup mode of all the tablespaces. GROUP 2 /u02/PRODCLON/oradata/redo02.log SIZE 5M, Copy create_db.sql to /oracle/admin/dbrefresh/ on Target Server. If PROD database is using spfile create pfile. Note: Check the logfile, datafiles & instance status, ***************************************************, Clone an Oracle Database using Hot Physical Backup.

This copy may be used for development, production testing, beta testing, etc, but rest assured that this need will arise and management will ask you to perform this task quickly. oraenv) environment setup script, Login as oracle on target database server and run the script /oracle/admin/dbrefresh//pre_refresh.sh and validate, Please note the Size of the TEMP tablespace, To restore the hotbackup set or copy the following files to destination server. In this topic, we are going to learn about Oracle Clone Database. (DESCRIPTION = 8. 1.Create the appropriate folder in corresponding path & placed the backup files in corresponding folder. If the init.ora file has to be copied then make sure apart from other changes the following mandatory parameters have to be changed. -. 5.Create the control file for cloning database. recover database using backup controlfile until cancel; 16. CHARACTER SET AL32UTF8; 14. Create a directory structure for the clone database and specify the same, 11. 2. Edit the clone database parameter file and make necessary changes to the clone database. Throughout the article, I will refer to environment variables by their UNIX notation (example $ORACLE_HOME). select name from v$datafile; GROUP 2 /u02/PRODCLON/oradata/redo02.log SIZE 5M,

/u02/PRODCLON/oradata/sysaux01.dbf, MAXLOGFILES 16 The datafiles from the production database can be from a hot backup, a cold backup or an RMAN backup. Select * from v$backup; (refer the Change#, Time column). Select name from v$datafile; CREATE CONTROLFILE SET DATABASE PRODCLON RESETLOGS ARCHIVELOG Clone means an exact copy. (3) Add the appropriate entries into the tnsnames.ora file in the $ORACLE_HOME/network/admin directory to allow connections to the target database from the duplicate server. >, Select checkboxes from the left navigation to add pages to your PDF. Now controlfile created. viinitPRODCLON.ora DATAFILE CREATE CONTROLFILE SET DATABASE clone RESETLOGS FORCE LOGGING NOARCHIVELOG . MAXINSTANCES 1 (LogOut/ RMAN> BACKUP DATABASE PLUS ARCHIVELOG. Create the control file by running from the trace path. . Database cloning is a procedure by which you can create an exact copy of an Oracle database without doing a full export/import. From the Destination server list, select the client that you want the clone to be on. startup. MAXLOGHISTORY 453 10. oraenv) environment setup script, Recover the database using backup controlfile, no need to apply archive log files, but online redolog files may be applied in some occasionand create temp files. After copying all datafiles, release the database from the backup mode. Note down the oldest log sequence number. You can create a clone from an existing data and log volume full backup. 2. (Refer: Step 8), type CANCEL to end the media recovery. This technique uses to export Oracle object that gets dropped somehow from the production database. (8) Now we need to connect auxiliary instance (db instance used in the recovery process to perform the recovery work). 5. Oracle cloning procedure mostly used by DBA to provide a full-sized DEV instance to the developers. /u02/PRODCLON/oradata/system01.dbf, 4. Change), You are commenting using your Twitter account. background_dump_dest=/u02/PRODCLON/bdump 4. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf using os command to copy the datafile. (6) Now connact to the duplicate instance as, $ ORACLE_SID=DB11G; export ORACLE_SID (LogOut/ Copy all data files, control files, and redo log files of the PROD database to a target database location. DATAFILE SPFILE Moving Oracle database to new Storage media. If you clone a database to an instance that resides on a proxy computer, the following criteria must be met: The Oracle database version on the proxy computer must be the same as the one in the source database. The system automatically shuts down the clone database and frees up all resources (including the snapshot clones on the storage array and ASM disk groups) when the reservation period expires. 6.Taken the controlfile backup viinitPRODCLON.ora Note:db_file_name_convert and log_file_name_convert parameters are required only if the source database directory structure and clone database directory structure differs. (2) Create a password file for the duplicate instance. /U01/oradata/clone/system01.dbf, (3) Uses the RMAN repository to select the backups for restoring the data files to the auxiliary instance. MAXLOGHISTORY 292 To perform Database Cloning, require database information as given below: Here file name and path is assumed, it can vary user to use depends on the installation. GROUP 1 /u02/PRODCLON/oradata/redo01.log SIZE 5M, (SID_DESC = Verify that the Oracle database version on the destination database is the same as the one on the source database. (SID_LIST = Create an appropriate directory structure in the clone database for dumps and specify them in the parameter file. . :wq! Oracle Database Cloning is nothing but a procedure that is used to create an identical database of the existing Oracle database. Start the clone database in NOMOUNT phase. >, Software Upgrades, Updates, and Uninstallation MAXLOGMEMBERS 3 LOGFILE Well go ahead with a brief explanation of how cloning operations can be performed in all these three methods. By default, the clone operation creates the control file and temp files in the staging path. (Database in windows we need to create the service id using oradim utility). Relocating an Oracle database to another machine. Using backup controlfile trace to generate the create controlfile scripts.

$ orapwd file=filename password=password entries=max_users. 2. steps: Keep the Database in Archive log or No archive log mode backup Policy. In the Oracle home box, type the full path to the destination Oracle home directory. MAXLOGHISTORY 292 Issue the command Alter database backup controlfile to trace on the source database, modify it so as to create a database (set the database name, change noresetlogs to resetlogs & change ARCHIVELOG to NOARCHIVELOG) and name as create_db.sql. (LogOut/ . You will be prompted to feed the archive log files henceforth. GROUP 2 /U01/oradata/clone/redo02.log SIZE 200M, In backup-based duplication, RMAN creates the duplicate database by using pre-existing RMAN backups and copies. RMAN copies the live source database over the network to the auxiliary instance.Here no backups of the source database are required. Take the list of Archive log files generated during(hot) backup and copy to target server for the recover process. (SERVICE_NAME = PRODCLON) 1. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); or clone a database from a backup or from an active database using DUPLICATE command to copy all the data in a source database. set oracle environment variables ORACLE_SID ORACLE_HOME or Use (. Change), You are commenting using your Twitter account. (7) Start the database in NOMOUNT mode using pfile which is created earlier. viinitPRODCLON.ora This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. LOGFILE Alter all the tablespaces to be put into the begin backup mode. ). The cloning technique is used to make a copy of the existing Database to perform various test activities by DBA like backup and recovery. Edit controltrace..trc file so as to create a database (set the database name, change noresetlogs to resetlogs & change ARCHIVELOG to NOARCHIVELOG) and name as create_db.sql. user_dump_dest=/u02/PRODCLON/udump To clone the database from a point in time, in the Recover to section, select Point in time. Those are listed below. 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. 8. 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. The first one that comes to mind is, of course, export and import. Create an appropriate directory structure for the clone database, 3. Hadoop, Data Science, Statistics & others. (4) Restores and copies the duplicate data files and recovers them with incremental backups and archived redo log files to a noncurrent point in time. (2) Mounts the restored or copied backup control file from the active database. Figure 24-2 illustrates active database duplication. /u02/PRODCLON/oradata/users01.dbf, . exit. If the Oracle database version is Oracle 12c, install a minimum version of Oracle 12.1.0.2 with patch 19404068. Take the control file trace backup to the trace path, 10. Cloning can be performed separately or on the same host. startup nomount pfile=/u02/PRODCLON/initPRODCLON.ora LOGFILE This technique is used to take a copy of the database before renaming that. export ORACLE_SID=PRODCLON

Data Source :Hotbackup-Production Database, Source : Export Backup(Production Database). datafile init.ora archivelog files control_trace_file. (Refer below example) cd /u02/PRODCLON Then in the From time and To time boxes, click the calendar to specify the dates. 17. To restore the coldbackup set or copy the following files to destination server. select member from v$logfile; The default value is 2048 MB. Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. select name from v$controlfile; alter database backup controlfile to trace; create pfile=/u02/PRODCLON/initPRODCLON.ora from spfile; If the database is using pfile, use OS command to copy the pfile to a backup location. Hot Cloning method is more suitable for a 24*7*365 running database. db_name=PRODCLON In the Destination details section, provide the destination information. db_name=PRODCLON 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. db_name=PRODCLON (6) Creates a new control file, which then creates and stores the new DBID in the data files. There are three methods to perform Oracle Database Cloning. It is a reliable method to perform Oracle Database Cloning. ) oraenv) environment setup script, Use env | grep ORA to verify the oracle setup, Login as oracle on target database server and run the script /oracle/admin/dbrefresh//pre_refresh.sh and validate, Please note the Database is in Archive log or No Archive log mode, Please note the TEMP tablespace size of the target Database, Shutdown immediate the database on target database server, Clean up all the datafiles,control files and log files of the target. In Oracle, Recovery Manager (RMAN) has the ability to duplicate or clone a database from a backup or from an active database using DUPLICATE command to copy all the data in a source database. Edit oratab file and put database in DONTMONITOR mode Shutdown the database on source database server. (5) Make the backup files from the source database available to the destination server using scp command (linux copy command). GROUP 3 /U01/oradata/clone/redo03.log SIZE 200M GROUP 1 /U01/oradata/clone/redo01.log SIZE 200M,

startup nomount pfile=/u02/PRODCLON/initPRODCLON.ora. /u02/PRODCLON/oradata/undotbs01.dbf, Determine the backup that you want to clone. Start RMAN, make PROD as a target, and PRODCLON as auxiliary. Metadata comes from backups. alter system switch logfile; Edit the clone database parameter file and make necessary changes to the clone database. That copy of the database can be used for production testing, beta testing, etc. Verify that the Oracle user in both the source and destination database servers has the same user ID. 5.Repeat the steps 1-3 for all your tablespaces. To clone the database from the most recent time, in the Recover to section, select Most recent backup. Copy all archive log files generated during the first old log sequence no.to the LAST old log sequence no. Change), You are commenting using your Facebook account. Create a free website or blog at WordPress.com. We will be changing that. MAXLOGFILES 50 (7) Opens the duplicate database with the RESETLOGS option and creates the online redo log for the new database. This method is used frequently by DBAs to update test or development environments from production. mkdir /u02/PRODCLON/oradata This is a guide to Oracle Clone Database. Change), You are commenting using your Facebook account. We just recreate the controlfile on the test machine using a new database name and do a recovery.

) archive log list; 5. 15. 7. sqlplus / as sysdba Depending on the resources available to companies, some have separate test and development databases and many roll testing and development into a single database. You can of course, resort to exporting and importing tablespaces if they are self contained and you are running version 8i or greater, enterprise edition. $ sqlplus / as sysdba. The proxy computer must have access to the storage array containing the source database volumes. Issue the command Alter database backup controlfile to trace on the source database, modify it so as to create a database (set the database name, change noresetlogs to resetlogs & change ARCHIVELOG to NOARCHIVELOG) and name as create_db.sql. In the Instance name box, type a name for the new instance. To perform a backup and recovery strategy. 3. . Your PDF is being created and will be ready soon. (1) Creates a default server parameter file for the auxiliary instance. DATAFILE This directory must be writable by the Oracle user. cp /u01/PROD/oradata/* /u02/PRODCLON/oradata/. To apply this method for database cloning, the Database has to be in archive log mode, and this method, no need to shut down the database. Clone an Oracle Database using Cold Physical Backup, 1. Login as oracle on target database server and run the script /oracle/admin/dbrefresh//pre_refresh.sh . (ADDRESS = (PROTOCOL = TCP)(HOST = 200.168.1.22)(PORT = 1521)) Create dd script if required. control_files=/u02/PRODCLON/oradata/cntrl01.ctl That is, you will copy the very datafiles from your production database and use it to create your test database. export ORACLE_SID=PROD This technique is very useful for moving the Oracle database to new storage media. This command restores files to the target database after which an incomplete recovery is performed and the clone database is opened using the RESETLOGS option. There are a few ways to do this. RMAN can perform backup-based duplication with or without either of the following connections: How RMAN Duplicates a Database Recover the database using a backup controlfile option. con_PRODCLON= MAXLOGMEMBERS 3 . CREATE CONTROLFILE SET DATABASE PRODCLON RESETLOGS ARCHIVELOG So your test database will be a mirror image of the production in all respects, except of course, for the database name. Get the file path information using below query. On the destination client where you want to create the clone database, create a directory for the mount path for the database clone files. MAXLOGMEMBERS 5 Configure the listener using listener.ora file and start the listener, SID_LIST_LISTENER = Note: The target database init.ora file can be reused, provided the SGA size and other parameter needs to be retained with same values.