How to clone a DB to another Server?
I want to copy an Oracle database from one server to another. I'm going to save the entire database into a CD and copy the exact database to another server. For the same server and operating system do the following. The DB will have the same name as the original.
If you want to change the DB name or directories, you must run the CREATE CONTROLFILE statement. You'll have to read the manual on that one; it is about 30 steps. Or you can do the following.
Using EXP and IMP
- Shut source database down with the NORMAL or IMMEDIATE option.
- Copy all datafiles. Select the name from v$datafile; to get their names.
- Copy all online redo logs. Select the member from v$logfile; to get their names.
- Copy all control files. Select the name from v$controlfile; to get their names.
- Copy the parameter file. Find it in $ORACLE_HOME/dbs in Unix and$ORACLE_HOME/database in Windows. The name is initDBNAME.ora where DBNAMEis database name.
- All of the files must be placed in directories that have same name as the source server directories. The names returned from the queries above are fully qualified with directory names.
- Create all of the directories specified in the parameter file you just copied. It will be BDUMP, CDUMP, UDUMP. Just recreate all of the directories you find specified in your parameter file.
- Edit the parameter file you copied. Set REMOTE_LOGIN_PASSWORDFILE to NONE if it is set SHARED or EXCLUSIVE.
- Start the database up.
- If you use a true temporary tablespace for sorting, you will have to recreate it. The database will still start, but the first sort that writes to it will bomb. Simple syntax is in the manuals.
- To use remote authentication again, run ORAPWD. Here's the syntax:
orapwd
Usage: orapwd file= password= entries=
where:
file = name of password file (mand)
password = password for SYS (mand)
entries = maximum number of distinct DBAs and OPERs (opt).
There are no spaces around the equal-to (=) character - Edit the parameter file to set REMOTE_LOGIN_PASSWORDFILE to SHARED or EXCLUSIVE (depending on what it was set to before).
- Restart the DB for it to take affect.
If you want to change the DB name or directories, you must run the CREATE CONTROLFILE statement. You'll have to read the manual on that one; it is about 30 steps. Or you can do the following.
Using EXP and IMP
- Create a database with a new name on the target server. Make it any database name you want with any directories you want.Since we will be using Export/Import, you will need to know this information:
- You must precreate the tablespaces if you are going to change directory names for your data files. Oracle looks for directory names during the IMPORT when it tries to create the TABLESPACES.
- If it can't find the directories, they will blow up.
- If you precreate them (anywhere you want), Oracle will know that the tablespaces are there and load them up with data. It won't blow up because it won't attempt to create them.
- Export the data from the source database. Set ORACLE_SID to source database name.exp system/manager file=full_export_dump.dmp log=full_export_log.out full=y
(We'll take defaults with everything else.) - Copy full_export_dump.dmp to the target server.
- Import the data into the target database. Set ORACLE_SID to the target database name.imp system/manager file=full_dump.dmp log=full_import_log.out full=y ignore=y
You only need to use ignore=y if your tablespaces are precreated. That means the database will ignore the "already exists" error that will be returned when it finds the tablespace is already there.
RMAN Based Cloning
Restore
database to new server using RMAN Backup
Steps On Source Database
**Most Of work will be done on target database
1- Get DBID By Using The below :
SQL > Select Db_id from v$database ;
Or RMAN target /
2- Get the last SCN On Database since we will not have active redolog using the below document :
SQL > select max(next_change#) from v$archived_log where archived = 'YES' group by thread#;
Or Using RMAN:
RMAN> list backup of archivelog all;
3- Take Backup Using RMAN :
run
{ allocate channel d1 type disk format 'YOUR-PATH/Database_%U;
allocate channel c1 type disk; allocate channel c2 type disk;
allocate channel c3 type disk;
BACKUP DATABASE PLUS ARCHIVELOG;
backup current controlfile format 'YOUR_PATH/controlfile_%U';
release channel c1;
release channel c2;
release channel c3;
}
Now move all generated backup file to the new host with the same directory for example if you backup on folder /u01/app then on new host should be the same.
Steps On New Server
After copy file do the below steps:
1- export ORACLE_SID=SID_SAME_AS_PROD
RMAN TARGET / RMAN > set dbid ;
RMAN > Startup nomount;
RMAN > restore spfile to pfile '/u01/app/oracle/oradata/orcl/initorcl.ora' from autobackup;
RMAN> shutdown abort;
Now you have to edit SPFile ;
*.audit_file_dest=’LOCATION’
*.control_files=’LOCATION’
*.db_recovery_file_dest_size=Size
*.db_recovery_file_dest=’LOCATION’
*.diagnostic_dest=’LOCATION’
*.log_archive_dest_1='LOCATION=LOCATION’
*.local_listener='LISTENER_ORCL'
RMAN> startup nomount pfile='/u01/app/oracle/oradata/orcl/initorcl.ora';
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
Note: now you have 2 options
1 - move the backup to same location on target server
2- Copy Backup to any location but you need to use CATALOG Command check below :
RMAN> catalog start with 'Where You copy Backup';
You are almost Done,
You have But before restore database you can move datafile to any location using (all the below steps on RMAN ) But Remember don't run the below yet:
SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/test.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf';
**Note: After TO: New Location.
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo01.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo02.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo03.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo03.log'' ";
Are you still having SCN that we see it above :)
Ok Now you RMAN Script will looks like below
RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/test.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf';
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo01.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo02.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo03.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo03.log'' ";
SET UNTIL SCN ;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
Sqlplus / as sysdba
SQL > Alter database open Resetlogs ;
Steps On Source Database
**Most Of work will be done on target database
1- Get DBID By Using The below :
SQL > Select Db_id from v$database ;
Or RMAN target /
2- Get the last SCN On Database since we will not have active redolog using the below document :
SQL > select max(next_change#) from v$archived_log where archived = 'YES' group by thread#;
Or Using RMAN:
RMAN> list backup of archivelog all;
3- Take Backup Using RMAN :
run
{ allocate channel d1 type disk format 'YOUR-PATH/Database_%U;
allocate channel c1 type disk; allocate channel c2 type disk;
allocate channel c3 type disk;
BACKUP DATABASE PLUS ARCHIVELOG;
backup current controlfile format 'YOUR_PATH/controlfile_%U';
release channel c1;
release channel c2;
release channel c3;
}
Now move all generated backup file to the new host with the same directory for example if you backup on folder /u01/app then on new host should be the same.
Steps On New Server
After copy file do the below steps:
1- export ORACLE_SID=SID_SAME_AS_PROD
RMAN TARGET / RMAN > set dbid ;
RMAN > Startup nomount;
RMAN > restore spfile to pfile '/u01/app/oracle/oradata/orcl/initorcl.ora' from autobackup;
RMAN> shutdown abort;
Now you have to edit SPFile ;
*.audit_file_dest=’LOCATION’
*.control_files=’LOCATION’
*.db_recovery_file_dest_size=Size
*.db_recovery_file_dest=’LOCATION’
*.diagnostic_dest=’LOCATION’
*.log_archive_dest_1='LOCATION=LOCATION’
*.local_listener='LISTENER_ORCL'
RMAN> startup nomount pfile='/u01/app/oracle/oradata/orcl/initorcl.ora';
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
Note: now you have 2 options
1 - move the backup to same location on target server
2- Copy Backup to any location but you need to use CATALOG Command check below :
RMAN> catalog start with 'Where You copy Backup';
You are almost Done,
You have But before restore database you can move datafile to any location using (all the below steps on RMAN ) But Remember don't run the below yet:
SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/test.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf';
**Note: After TO: New Location.
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo01.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo02.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo03.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo03.log'' ";
Are you still having SCN that we see it above :)
Ok Now you RMAN Script will looks like below
RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/test.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf';
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo01.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo02.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo03.log'' TO ''/u01/app/oracle/oradata/orcl/redo/redo03.log'' ";
SET UNTIL SCN ;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
Sqlplus / as sysdba
SQL > Alter database open Resetlogs ;