Saturday, April 16, 2011

Cloning through rman on different server with different directory structure and different DBNAME...

Here we will see how to clone a database from one server to another.


Lets say we have two servers:


server 1


server 2


Now we will going to clone the database from server1 to server2.


Target database:Noida


First take the full backup of target database from server 1 and note the current scn.

SQL>select current_scn from v$database.

CURRENT_SCN
-----------
1200598


Copy this full backup on server 2.


On server 1 perform the below activities:

Create the instance:

C:\>oradim -new -sid noida -intpwd noida -startmode m
Instance created.

C:\>set ORACLE_SID=noida

Create the password file:

C:\>orapwd file=D:\oracle\product\11.1.0\db_1\dbs\noidapwd12.ora entries=10

Enter password for SYS:

C:\>set ORACLE_SID=noida

C:\>sqlplus sys/noida as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Apr 16 13:58:25 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to an idle instance.

Modify the init parameter file and set the location for udump ,bdump and controlfiles.

Now, create the spfile from this parameter file..

SQL> create spfile from pfile='D:\bkp\orcl_bkp\noidainit.ora';

File created.

SQL> exit
Disconnected

C:\>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Apr 16 13:59:08 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     318046208 bytes

Fixed Size                     1332920 bytes
Variable Size                234883400 bytes
Database Buffers              75497472 bytes
Redo Buffers                   6332416 bytes


Restore controlfile from the copied backup....


RMAN> restore controlfile from 'D:\bkp\noida_bkp\cf\C-1502483083-20110416-00';

Starting restore at 16-APR-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL01.CTL
output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL02.CTL
output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL03.CTL
Finished restore at 16-APR-11

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


Catalog the backup of server 1 to server 2..

RMAN> catalog backuppiece 'D:\bkp\orcl_bkp\06M9U16S_1_1';

cataloged backup piece
backup piece handle=D:\BKP\ORCL_BKP\06M9U16S_1_1 RECID=8 STAMP=748621118

RMAN> catalog backuppiece 'D:\bkp\orcl_bkp\07M9U1BF_1_1';

cataloged backup piece
backup piece handle=D:\BKP\ORCL_BKP\07M9U1BF_1_1 RECID=9 STAMP=748621138

RMAN> catalog backuppiece 'D:\bkp\orcl_bkp\08M9U1K7_1_1';

cataloged backup piece
backup piece handle=D:\BKP\ORCL_BKP\08M9U1K7_1_1 RECID=10 STAMP=748621154


RMAN> run
2> {
3> set until scn 1200598;
4> set newname for datafile 1 to 'D:\oracle\oradata\noida\DATAFILES\system01.dbf
';
5> set newname for datafile 2 to 'D:\oracle\oradata\noida\DATAFILES\sysaux01.dbf
';
6> set newname for datafile 3 to 'D:\oracle\oradata\noida\DATAFILES\UNDOTBS01.DB
F.dbf';
7> set newname for datafile 4 to 'D:\oracle\oradata\noida\DATAFILES\users01.dbf'
;
8> set newname for datafile 5 to 'D:\oracle\oradata\noida\DATAFILES\EXAMPLE01.DB
F.dbf';
9> restore database ;
10> switch datafile all;
12>  }

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-APR-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\ORACLE\ORADATA\NOIDA\DATAFILE
S\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\NOIDA\DATAFILE
S\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\NOIDA\DATAFILE
S\UNDOTBS01.DBF.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\NOIDA\DATAFILE
S\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\ORACLE\ORADATA\NOIDA\DATAFILE
S\EXAMPLE01.DBF.DBF
channel ORA_DISK_1: reading from backup piece D:\BKP\ORCL_BKP\07M9U1BF_1_1
channel ORA_DISK_1: piece handle=D:\BKP\ORCL_BKP\07M9U1BF_1_1 tag=TAG20110416T13
4452
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:56
Finished restore at 16-APR-11

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=748621662 file name=D:\ORACLE\ORADATA\NOIDA\DA
TAFILES\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=748621663 file name=D:\ORACLE\ORADATA\NOIDA\DA
TAFILES\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=748621665 file name=D:\ORACLE\ORADATA\NOIDA\DA
TAFILES\UNDOTBS01.DBF.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=748621667 file name=D:\ORACLE\ORADATA\NOIDA\D
ATAFILES\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=748621669 file name=D:\ORACLE\ORADATA\NOIDA\D
ATAFILES\EXAMPLE01.DBF.DBF

Note that the directory structure “D:\ORACLE\ORADATA\NOIDA\DATAFILE\” on server 2 is different from server 1 “D:\ORACLE\ORADATA\NOIDA\”

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1         13   52428800          1 NO  CURRENT
      1195448 16-APR-11

         3          1         12   52428800          1 YES ACTIVE
      1191704 16-APR-11

         2          1         11   52428800          1 YES INACTIVE
      1187896 16-APR-11



RMAN> recover database until logseq 13;

Starting recover at 16-APR-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:05

Finished recover at 16-APR-11

RMAN> exit


Recovery Manager complete.

SQL> alter database open resetlogs;

Database altered.


Now,crosscheck some data..

SQL> select * from hr.test;

        ID
----------
        12
        23
        34
        45
        56
        67
        78
        89
        11
        22
        33

        ID
----------
        44
        55
        66
        77
        88
        99
       111
       222
       444
       333
      5555

22 rows selected.

SQL> select * from test;

        ID
----------
        12
        23
        34
        45
        56
        67
        78
        89

8 rows selected.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
NOIDA     READ WRITE


Changing DBNAME now!

You may change the DBNAME as explained in my blog "Changing database name and DBID…looks exciting!"

:-) all the best