| Note: This instructions should work on any version of | | | | CREATE CONTROLFILE REUSE DATABASE |
| Oracle database. | | | | "testdb" NORESETLOGS ARCHIVELOG |
| People describe database cloning in different ways. | | | | MAXLOGFILES 64 |
| Basically Oracle database cloning simply means | | | | MAXLOGMEMBERS 5 |
| copying an Oracle database from another database. | | | | MAXDATAFILES 64 |
| For example you want to refresh your test oracle | | | | MAXINSTANCES 32 |
| database using production data. In that case you can | | | | MAXLOGHISTORY 17871 |
| do database cloning by simply getting rid of your test | | | | LOGFILE |
| databaseand recreating it with production database | | | | GROUP 1 ('/u01/oradata/testdb/oralog1a.rdo', |
| including data. | | | | ‘/u02/oradata/testdb/oralog1b.rdo') SIZE 25M, |
| You may need/want to clone a database for any | | | | GROUP 2 ('/u01/oradata/testdb/oralog2a.rdo', |
| number of reasons such as: | | | | ‘/u02/oradata/testdb/oralog2a.rdo') SIZE 25M |
| - 1. Your database is somehow corrupted | | | | DATAFILE |
| - 2. Your database rollback segment or other | | | | ‘/u01/oradata/testdb/system01.dbf', |
| tablespaces are corrupt and you can not recover | | | | ‘/u01/oradata/testdb/mydatabase.dbf', |
| them | | | | ‘/u02/oradata/testdb/app_data_01.dbf', |
| - 3. You simply want to refresh your database from | | | | ‘/u02/oradata/testdb/app_data_02.dbf', |
| another database | | | | ‘/u02/oradata/testdb/app_index_01.dbf' |
| Whatever the reasons are, the cloning steps remain | | | | ; |
| mainly same. Here are the steps that you need to | | | | # Recovery is required if any of the datafiles are |
| execute to clone Oracle database. | | | | restored backups, |
| Assumptions: | | | | # or if the last shutdown was not normal or immediate. |
| 1. You can not OPEN your corrupt database but you | | | | RECOVER DATABASE |
| can login as sysdba. | | | | # All logs need archiving and a log switch is needed. |
| 2. You have access to a functioning database which | | | | ALTER SYSTEM ARCHIVE LOG ALL; |
| you want to copy | | | | # Database can now be opened normally. |
| 3. You have access DBA credentials | | | | ALTER DATABASE OPEN; |
| Step1: Make a list of your logfiles, datafiles and | | | | 5. Now copy the content of this file in your desktop in |
| controlfiles of your corrupt database. You will need this | | | | a test file called |
| list to delete them physically. | | | | "create_controlfile.sql". |
| Logon to the corrupt database as:sqlplus / as | | | | Remove the lines "RECOVER DATABASE" and |
| sysdbaorsvrmgrlconnect / as sysdba | | | | "ALTER SYSTEM ARCHIVE LOG ALL"and "ALTER |
| Run following commands to get the list of your logfiles, | | | | DATABASE OPEN;" from create_controlfile.sql; |
| datafiles etc:select name from v$controlfile;select * | | | | 6. Now shutdown your functioning database and exit, |
| from v$logfile;select name from v$datafile; | | | | runshutdown immediate;exit; |
| Step2: Shutdown the corrput database usingshutdown | | | | Step5. Once database is shutdown the next step is to |
| abort; | | | | copy all files fromyour functioning database to the new |
| Step3. Physically delete all logfiles, datafiles and | | | | location (machine of your corrupt database). |
| controlfiles from the machine. | | | | If you need to ftp the files then always use "bin". |
| Step4: Logon to functioning database machine. | | | | Copy all these files (logfiles, datafiles and controlfiles |
| Now logon to the database assqlplus / as | | | | and init.ora) in exactly same locations as they are in |
| sysdbaorsvrmgrlconnect / as sysdbawhatever is | | | | the functionning database machine (check |
| applicable to you according to yuor Oracle database | | | | create_controlfile.sql for reference). If you need to |
| version. | | | | create directoriesthen create them. Physical location of |
| 3. Runalter database backup controlfile to trace; | | | | each of these file should be exactly same. |
| This command will generate a text formatted trace file | | | | Step6. When all files are copied to their correct |
| that will have the physical structure of the functioning | | | | locations, go to Machine where your database is |
| oracle database. You are going to need this file later. | | | | corrupted. |
| 4. Runshow parameter user_dump_dest; | | | | Then login to oracle assqlplus / as |
| This command will give you the location of your Oracle | | | | sysdbaorsvrmgrlconnect / as sysdba |
| trace file generated on | | | | Step7: Now run the controlfile script: |
| Step4.3 | | | | @create_controlfile.sql; |
| 5. From another session to your functioning database | | | | Your cloned Oracle database should now come up. |
| machine go to the location of your user_dump_dest | | | | Login to your database to see if it is functioning |
| and make sure there is a trace file and it looks | | | | correctly. |
| something like this: | | | | If you have question let me know. |
| STARTUP NOMOUNT | | | | |