Copy Oracle 10g database to another location

Source Database Name: ITDQF

Clone Database Name: ITDQF

Steps to be followed:

SOURCE DB

SQL> startup mount; ORACLE instance started. Total System Global Area 1887350784 bytes Fixed Size                  2289688 bytes Variable Size             570429416 bytes Database Buffers         1308622848 bytes Redo Buffers                6008832 bytes Database mounted. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u02/oradata/ITDQF/datafile/o1_mf_system_czl74trh_.dbf /u02/oradata/ITDQF/datafile/o1_mf_sysaux_czl72smq_.dbf /u02/oradata/ITDQF/datafile/o1_mf_undotbs1_czl76x03_.dbf /u02/oradata/ITDQF/datafile/o1_mf_users_czl76vwr_.dbf SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u02/oradata/ITDQF/onlinelog/o1_mf_3_czl77kth_.log /u03/fast_recovery_area/ITDQF/onlinelog/o1_mf_3_czl77kwz_.log /u02/oradata/ITDQF/onlinelog/o1_mf_2_czl77jhv_.log /u03/fast_recovery_area/ITDQF/onlinelog/o1_mf_2_czl77kl0_.log /u02/oradata/ITDQF/onlinelog/o1_mf_1_czl77hfv_.log /u03/fast_recovery_area/ITDQF/onlinelog/o1_mf_1_czl77hj2_.log 6 rows selected. SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u02/oradata/ITDQF/controlfile/o1_mf_czl77f98_.ctl /u03/fast_recovery_area/ITDQF/controlfile/o1_mf_czl77fbg_.ctl SQL> SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/controlfile.sql'; Database altered. SQL> create pfile='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/initITDQF.ora' from spfile; File created. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL>

TARGET

Remove everything up to the "START NOMOUNT" statement and everything after the semicolon at the end of the "CREATE CONTROLFILE" statement. Edit the line starting with "CREATE CONTROLFILE" and replace the word "REUSE" with the word "SET" right before the keyword DATABASE.  On the same line, modify the database name changing it from SOURCE to TARGET.  On the same line, change the keyword NORESETLOGS to RESETLOGS. Change datafile where necessary. remove blank lines and comments.

STARTUP NOMOUNT pfile=’/u01/app/oracle/product/12.1.0/dbhome_2/dbs/initITDQF.ora’ CREATE CONTROLFILE SET DATABASE "ITDQF" RESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292 LOGFILE  GROUP 1 (    '/u04/oradata/ITDQF/onlinelog/o1_mf_1_czl77hfv_.log',    '/u05/fast_recovery_area/ITDQF/onlinelog/o1_mf_1_czl77hj2_.log'  ) SIZE 50M BLOCKSIZE 512,  GROUP 2 (    '/u04/oradata/ITDQF/onlinelog/o1_mf_2_czl77jhv_.log',    '/u05/fast_recovery_area/ITDQF/onlinelog/o1_mf_2_czl77kl0_.log'  ) SIZE 50M BLOCKSIZE 512,  GROUP 3 (    '/u04/oradata/ITDQF/onlinelog/o1_mf_3_czl77kth_.log',    '/u05/fast_recovery_area/ITDQF/onlinelog/o1_mf_3_czl77kwz_.log'  ) SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE  '/u04/oradata/ITDQF/datafile/o1_mf_system_czl74trh_.dbf',  '/u04/oradata/ITDQF/datafile/o1_mf_sysaux_czl72smq_.dbf',  '/u04/oradata/ITDQF/datafile/o1_mf_undotbs1_czl76x03_.dbf',  '/u04/oradata/ITDQF/datafile/o1_mf_users_czl76vwr_.dbf' CHARACTER SET AL32UTF8 ;

[root@TESTITD database]# mkdir -p /u04/oradata/ITDQF [root@TESTITD database]# chown -R oracle:oinstall /u04 [root@TESTITD database]# chmod -R 775 /u04 [root@TESTITD database]# mkdir -p /u05/fast_recovery_area/ITDQF [root@TESTITD database]# chown -R oracle:oinstall /u05 [root@TESTITD database]# chmod -R 775 /u05 Copy data files and redo log files ( v$datafile, v$logfile ) Do not copy v$controlfile -- it will be created in next step Do not copy v$tempfile -- it will be created in step 6 SOURCE could startup [oracle@TESTITD ITDQF]$ ll total 24 drwxr-x---. 2 oracle oinstall 12288 Oct  8 11:42 adump drwxr-x---. 2 oracle oinstall  4096 Oct  8 10:25 dpdump drwxr-x---. 2 oracle oinstall  4096 Oct  8 10:25 pfile drwxr-x---. 2 oracle oinstall  4096 Oct  8 10:18 xdb_wallet [oracle@TESTITD ITDQF]$ mv adump adump.old [oracle@TESTITD ITDQF]$ mv pfile pfile.old [oracle@TESTITD ITDQF]$ mkdir adump [oracle@TESTITD ITDQF]$ mkdir pfile [oracle@TESTITD ITDQF]$ change in initTARGET.ora: db_name, control_files, user_dump_dest, background_dump_dest, core_dump_dest and may be audit_file_dest,log_archive_dest [oracle@TESTITD ~]$ vi /u01/app/oracle/product/12.1.0/dbhome_2/dbs/initITDQF.ora ITDQF.__data_transfer_cache_size=0 ITDQF.__db_cache_size=1308622848 ITDQF.__java_pool_size=16777216 ITDQF.__large_pool_size=150994944 ITDQF.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment ITDQF.__pga_aggregate_target=637534208 ITDQF.__sga_target=1895825408 ITDQF.__shared_io_pool_size=0 ITDQF.__shared_pool_size=402653184 ITDQF.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/ITDQF/adump' *.audit_trail='db' *.compatible='12.1.0.0.0' *.control_files='/u04/oradata/ITDQF/controlfile/o1_mf_czl77f98_.ctl','/u05/fast_recovery_area/ITDQF/controlfile/o1_mf_czl77fbg_.ctl' *.db_block_size=8192 *.db_create_file_dest='/u04/oradata' *.db_domain='' *.db_name='ITDQF' *.db_recovery_file_dest='/u05/fast_recovery_area' *.db_recovery_file_dest_size=4800m *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ITDQFXDB)' *.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.136)(PORT=1521))' *.open_cursors=300 *.pga_aggregate_target=598m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1794m *.undo_tablespace='UNDOTBS1'

export ORACLE_SID=ITDQF export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2 export ORACLE_BASE=/u01/app/oracle export PATH=$PATH:$ORACLE_HOME/bin SQL> @controlfile.sql ORACLE instance started. Total System Global Area 1887350784 bytes Fixed Size                  2289688 bytes Variable Size             570429416 bytes Database Buffers         1308622848 bytes Redo Buffers                6008832 bytes Control file created. SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database open resetlogs; Database altered. SQL>  ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/oradata/ITDQF/datafile/temp_01.dbf' size 2G; Tablespace altered. SQL> select name, open_mode from v$database; NAME      OPEN_MODE --------- -------------------- ITDQF     READ WRITE SQL>