Oracle AppsDBAs World: Database Cloning

Database Cloning
--------------------

1. Ensure that your database is recoverable back to original location and state by backing it up before executing this procedure:

Database in NON-ARCHIVE LOG MODE - take an off-line database backup.
Database in ARCHIVELOG MODE - take an on-line or off-line database backup.

2. Connect to sql*plus as the user ‘SYS AS SYSDBA’ and ensure that all users have logged off the system by executing the following command.

SQL> connect / as sysdba
Connected.
SQL> select username from v$session;
SQL> select username from v$session;
USERNAME

SYS
8 rows selected.
SQL>

You should see several fields returning with no username (these are internal oracle logins and the will not affect your operations) and one SYS login (this is you).

3. Place the system in restricted mode:

SQL> alter system enable restricted session;

System altered.

4. Connect as SYS with SYSDBA privileges and execute the following SQL command:

SQL> connect / as sysdba
Connected.
SQL> alter database backup controlfile to trace;
SQL>

The [alter database backup controlfile to trace;] command will generate a SQL script that can be used to regenerate the database.

5. Execute the sql*plus command show parameter to find your user dump directory.

SQL> show parameter user_dump_dest
NAME TYPE VALUE
-------------------------- ----------- ------------------------------
user_dump_dest string /opt/app/oracle/product/admin/orcl/udump

6. Exit to the OS layer and move to the user_dump_dest directory. Trace file names are generated by oracle in the following format [instance_name]+[oracle generated number].trc, so use the timestamp to locate the last trace file generated. Open the file in a text editor and remove everything above the ‘STARTUP NOMOUNT ..' statement. Alter the ‘CREATE CONTROLFILE’ statement for the following uses:

Rename DB in Archivelog mode:
CREATE CONTROLFILE SET DATABASE "new_db_name" RESETLOGS
ARCHIVELOG

Rename DB in Non-Archivelog mode:
CREATE CONTROLFILE SET DATABASE "new_db_name" NORESETLOGS
NOARCHIVELOG

Reuse DB name in Archivelog mode:
CREATE CONTROLFILE REUSE DATABASE "db_name" RESETLOGS ARCHIVELOG

Reuse DB name in Non-Archivelog mode:
CREATE CONTROLFILE REUSE DATABASE "db_name" NORESETLOGS
NOARCHIVELOG

7. Edit the rest of this file to reference the correct locations of you log files, datafiles and temp files.

8. Move the trace file to an appropriate location and name to recreate_db.sql.

9. Connect as SYS with SYSDBA privileges and shutdown the database in immediate mode:
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate

10. Move the following files to there corresponding locations in updated recreate_db.sql file.
DATAFILES
Log files

11. Restore or copy the backup and the archived logs (if any) to the new machine.

12. Move your INIT$ORACLE_SID.ORA file to its new location. Ensure your controlfile and archive destinations in INIT are set properly to the new locations.

13. Connect to SQLPLUS on your new machine as SYS AS SYSDBA and run create_db.sql script as follows:

SQL> @[directory_location]/create_db.sql

14. Change the global database name if you renamed your database as follows:

SQL> ALTER DATABASE RENAME GLOBAL_NAME = new_db_name;

15. Perform full system backup and store in a safe location.

posted by Senthil Kumaran @ 10:24 AM

0 Comments: