Friday, July 1, 2011

Create New Database using Image Copy Backup in Oracle

Suppose the source database is T1 and we want to create a new database T9 from it.

1. Connect to T1 database using sqlplus as sysdba:

C:\Documents and Settings\Administrator>set ORACLE_SID=T1

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 1 15:37:12 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

2. Create a pfile named initt9.ora from the spfile and store it into the ORACLE_HOME\database folder

SQL> create pfile='c:\oracle\product\10.2.0\db_1\database\initt9.ora' from spfile;

File created.


3. Open the initt9.ora and make the changes in it such that instead of t1 it reflects t9. A sample initt9 file will look like this:

t9.__db_cache_size=415236096
t9.__java_pool_size=16777216
t9.__large_pool_size=4194304
t9.__shared_pool_size=171966464
t9.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0\admin\t9\adump'
*.background_dump_dest='C:\oracle\product\10.2.0\admin\t9\bdump'
*.compatible='10.2.0.3.0'
*.control_files='C:\oracle\product\10.2.0\oradata\t9\control01.ctl','C:\oracle\product\10.2.0\oradata\t9\control02.ctl','C:\oracle\product\10.2.0\oradata\t9\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0\admin\t9\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='t9'
*.db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=t9XDB)'
*.job_queue_processes=10
*.log_archive_format='ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0\admin\t9\udump'

4. Create the necessary folders as required by looking into the pfile. Specifically create folders:
  • C:\oracle\product\10.2.0\admin\t9\pfile
  • C:\oracle\product\10.2.0\admin\t9\adump
  • C:\oracle\product\10.2.0\admin\t9\bdump
  • C:\oracle\product\10.2.0\admin\t9\cdump
  • C:\oracle\product\10.2.0\admin\t9\dpdump
  • C:\oracle\product\10.2.0\admin\t9\udump
  • C:\oracle\product\10.2.0\oradata\t9
  • C:\oracle\product\10.2.0\flash_recovery_area\t9

5. Create a servie for the t9 instance:
C:\Documents and Settings\Administrator>oradim -new -sid t9
Instance created.


6. Create a password file for the t9 instance in the ORACLE_HOME\database folder:
C:\Documents and Settings\Administrator>orapwd file=c:\oracle\product\10.2.0\db_
1\PWDt9.ora password=oracle

7. Connect to the t1 instance and create a trace file of the control file:
C:\Documents and Settings\Administrator>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 1 15:55:17 2011
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup controlfile to trace as 'c:\control.txt';

Database altered.

8. Open theControlfile trace and do the following task:
  • Delete all the lines starting with --
  • Delete the section that specifies how to recreate the controlfile and open it in NORESETLOGS mode.
  • Change the entries of T1 to T9 
  • Where it is written CREATE CONTROLFILE REUSE DATABASE "T1" RESETLOGS change it to CREATE CONTROLFILE SET DATABASE "T9" RESETLOGS
  • Remove the line that reads  RECOVER DATABASE USING BACKUP CONTROLFILE
At the end the trace file should read something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "T9" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\REDO01.LOG'  SIZE 50M,
  GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\REDO02.LOG'  SIZE 50M,
  GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\REDO03.LOG'  SIZE 50M
DATAFILE
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\SYSTEM01.DBF',
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\UNDOTBS01.DBF',
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\SYSAUX01.DBF',
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\USERS01.DBF',
  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;

VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

ALTER DATABASE REGISTER LOGFILE

'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\T9\ARCHIVELOG\2011_07_01\O1_MF_1_1_%U_.ARC';
ALTER DATABASE REGISTER LOGFILE

'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\T9\ARCHIVELOG\2011_07_01\O1_MF_1_1_%U_.ARC';

RECOVER DATABASE USING BACKUP CONTROLFILE


ALTER DATABASE OPEN RESETLOGS;


ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\TEMP01.DBF'SIZE 29360128  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

 9. Shutdown the T1 database:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

10. Copy all the datafiles and Online Redo Log Files from the C:\oracle\product\10.2.0\oradata\t1 to the folder C:\oracle\product\10.2.0\oradata\t9
Note: Remember not to copy the controlfiles.

11. Startup the t9 instance in nomount mode:

C:\Documents and Settings\Administrator>set ORACLE_SID=t9
C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 1 16:15:58 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292060 bytes
Variable Size             197134564 bytes
Database Buffers          411041792 bytes
Redo Buffers                2899968 bytes


12. Set the oracle environment to t9 and run the controlfile script created priviously:

SQL> CREATE CONTROLFILE SET DATABASE "T9" RESETLOGS  ARCHIVELOG MAXLOGFILES 16 M
AXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1
 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\REDO01.LOG'  SIZE 50M, GROUP 2 'C:\ORACLE\
PRODUCT\10.2.0\ORADATA\T9\REDO02.LOG'  SIZE 50M, GROUP 3 'C:\ORACLE\PRODUCT\10.2
.0\ORADATA\T9\REDO03.LOG'  SIZE 50M DATAFILE  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\
T9\SYSTEM01.DBF',   'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\UNDOTBS01.DBF', 'C:\ORA
CLE\PRODUCT\10.2.0\ORADATA\T9\SYSAUX01.DBF', 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T
9\USERS01.DBF',  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\T9\EXAMPLE01.DBF' CHARACTER S
ET WE8MSWIN1252;

Control file created.

13. Open the database using the resetlogs option:
SQL> alter database open resetlogs;
Database altered.

14. Check the status of the T9 database from the v$instance view:
SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
t9               OPEN

15. If you want you can configure the enterprise manager database control for T9 database as well:

C:\Documents and Settings\Administrator>emca -config dbcontrol db

STARTED EMCA at Jul 1, 2011 4:40:29 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: t9
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ C:\oracle\product\10.2.0\db_1

Database hostname ................ Lab142
Listener port number ................ 1521
Database SID ................ t9
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Jul 1, 2011 4:40:48 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\t9\emca_2011-07-01_04-40-29-PM.log.
Jul 1, 2011 4:40:52 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jul 1, 2011 4:41:32 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jul 1, 2011 4:41:33 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://Lab142:5502/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 1, 2011 4:41:33 PM

16. Access the enterprise manager database console to verify:


Cheers!!!
Posted by at No comments:
Subscribe to: Comments (Atom)