Objective :-We are cloning a database running on target server and restoring it into clone server with a different sid.
Note- Make sure oracle binary is already installed on target db server.In this case the directory structure of target server is different than source server.
Database Version :- Oracle Enterprise Edition 11.2.0.1 on RHEL 6.4 on both server
Source Server: 192.168.0.102
Database sid:- prim
Hostname:-server1.soumya.com
Destination Server: 192.168.0.104
database sid: newprim
Hostname:-server2.soumya.com
1.Take backup of controlfile as trace:[SOURCE DB]
SQL> alter database backup controlfile to trace as '/u01/app/oracle/control01.sql';
Database altered.
2. check the location of datafiles[SOURCE DB]
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/temp01.dbf
3. Shutdown the database:[SOURCE DB]
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4. Copy the datafiles to the target db server
[oracle@server1 ]$cd /u01/app/oracle/oradata/prim
[oracle@server1 backup]$ scp *.dbf oracle@server2:/u01/
The authenticity of host 'server2 (192.168.0.104)' can't be established.
RSA key fingerprint is 0b:59:e4:8b:b1:e6:12:3a:38:4f:ba:74:ef:8a:ad:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server2,192.168.0.104' (RSA) to the list of known hosts.
oracle@server2's password:
example01.dbf 100% 100MB 25.0MB/s 00:04
sysaux01.dbf 100% 500MB 16.7MB/s 00:30
system01.dbf 100% 670MB 19.7MB/s 00:34
temp01.dbf 100% 20MB 6.7MB/s 00:03
undotbs01.dbf 100% 55MB 27.5MB/s 00:02
users01.dbf 100% 5128KB 5.0MB/s 00:00
Also copy the controlfile which we took backup at step 1 and transfer it into target server.
5.Make changes in the init file for target db:[TARGET DB]
We can copy the pfile from source db and the change the required parameters like DB_NAME and control_file localtion,audit_file_dest location,diag location etc.
[oracle@server2 dbs]$ cat initnewprim.ora
newprim.__db_cache_size=138412032
newprim.__java_pool_size=4194304
newprim.__large_pool_size=4194304
newprim.__oracle_base='/u01/newapp/oracle'#ORACLE_BASE set from environment
newprim.__pga_aggregate_target=167772160
newprim.__sga_target=247463936
newprim.__shared_io_pool_size=0
newprim.__shared_pool_size=92274688
newprim.__streams_pool_size=0
*.audit_file_dest='/u01/newapp/oracle/admin/newprim/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/newapp/oracle/oradata/newprim/control01.ctl','/u01/newapp/oracle/flash_recovery_area/newprim/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='newprim'
*.db_recovery_file_dest='/u01/newapp/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/newapp/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newprimXDB)'
*.memory_target=414187520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
Create necessary directories as per new sid
[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/admin/newprim/adump
[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/oradata/newprim/
[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/flash_recovery_area/newprim/
6. Now Start the database in nomount stage:[TARGET DB]
[oracle@server2 dbs]$ export ORACLE_SID=newprim
[oracle@server2 dbs]$sqlplus / as sysdba
SQL> startup nomount pfile='/u01/newapp/oracle/product/11.2.0/db_1/dbs/initnewprim.ora';
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 268437496 bytes
Database Buffers 138412032 bytes
Redo Buffers 4308992 bytes
7. Re-recreate the controlfile [ TARGET DB ]
Open the controlfile and remove word REUSE and make it SET
remove word NORESETLOGS and make it RESETLOGS
remove or keep the word ARCHIVELOG depending upon our requirement
change database name from "prim" to "newprim"
change directory name everywhere from 'prim' to 'newprim'
:wq
The content should look like this
[oracle@server2 u01]$ vi control01.sql
CREATE CONTROLFILE SET DATABASE "NEWPRIM" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/newprim/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/newprim/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/newprim/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/newprim/system01.dbf',
'/u01/app/oracle/oradata/newprim/sysaux01.dbf',
'/u01/app/oracle/oradata/newprim/undotbs01.dbf',
'/u01/app/oracle/oradata/newprim/users01.dbf',
'/u01/app/oracle/oradata/newprim/example01.dbf'
CHARACTER SET WE8MSWIN1252
;
8.Create the controlfile at target db.
SQL>@/u01/control01.sql
Control file created.
9.Open the database in resetlog mode:
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
10.Create the temp files.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/newapp/oracle/oradata/newprim/temp_01.dbf' SIZE 50m autoextend on next 10m maxsize unlimited;
Tablespace altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
newprim
