RMAN Duplicate database from Active database with ASM Migration

 Oracle 11g introduced the new feature to Duplicate the database from active database. Duplicating from an active database doesn’t require any RMAN backup to be taken from the source database. It directly reads from active database. But this might cause negative performance impact for source database while duplicating the database. It could also cause network traffic between source and target database.  You can take this approach if your environment is accepting these risks. The RDBMS software should be installed on the auxiliary database server and the DB version should be same as target database server.


High Level steps :

1. Create all the directories in auxiliary server
2. Copy password file  & pfile from target server to auxiliary server
3. Start the instance(with nomount) in auxiliary instance
4. Check the connectivity between source and target
5. Duplicate the database in auxiliary instance.
6. create the spfile in ASM disk
7. Database verification and Validation

Target  DB info :

Step1  Create the necessary folders in auxiliary database.  

Here i  am migrating the database from file system to ASM disk. This case, we don't need to create directory in the file system. I created the directory in ASM disk.

[oracle@RACTEST1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir USBEN

Step2 Copy the password file and parameter file from target database server to auxiliary database server. Add entry in /etc/oratab for usben database.

Modify  the parameter file in auxiliary database according to the environment. Here is the modified pfile in auxiliary database.

initusben.ora

usben.__data_transfer_cache_size=0
usben.__db_cache_size=536870912
usben.__java_pool_size=16777216
usben.__large_pool_size=150994944
usben.__oracle_base='/ora/app/oracle'
usben.__pga_aggregate_target=671088640
usben.__sga_target=989855744
usben.__shared_io_pool_size=50331648
usben.__shared_pool_size=218103808
usben.__streams_pool_size=0
*.compatible='12.1.0.2.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='usben'
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/ora/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=usbenXDB)'
*.log_archive_dest_1='location=/backup/usben/arch'
*.log_archive_dest_state_1='enable'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'



Step 3  Start the instance using the above parameter with no mount mode:
















Step 4  Check the connectivity between source and target.  

Add entry in /etc/oratab for auxiliary  database. 

Adding the below entries in auxiliary database. DO NOT MAKE any changes in target database.

tnsnames.ora 

usdup =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = usben.localdomain)
    )
  )

usben =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = usbenhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = usben.localdomain)
    )
  )

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = usben.localdomain)
      (ORACLE_HOME = /ora/app/oracle/product/12.1.0.1/db_1)
      (SID_NAME = usben)
    )
  )

LISTENER =

  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = RACTEST1.localdomain)(PORT = 1522))
    )

  )

Start the listener in auxiliary database server.

[oracle@RACTEST1 dbs]$ lsnrctl

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-FEB-2016 17:32:52

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start
Starting /ora/app/oracle/product/12.1.0.1/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /ora/app/oracle/product/12.1.0.1/db_1/network/admin/listener.ora
Log messages written to /ora/app/oracle/diag/tnslsnr/RACTEST1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=RACTEST1.localdomain)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACTEST1.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                17-FEB-2016 17:32:54
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora/app/oracle/product/12.1.0.1/db_1/network/admin/listener.ora
Listener Log File         /ora/app/oracle/diag/tnslsnr/RACTEST1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=RACTEST1.localdomain)(PORT=1522)))
Services Summary...
Service "usben.localdomain" has 1 instance(s).
  Instance "usben", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Now check the connectivity between auxiliary and target database server.

restore_connectivity.sh

Here is the log file content for above shell script.

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Feb 17 09:05:45 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN>
connected to target database: USBEN (DBID=2379284599)

RMAN>
connected to auxiliary database: USBEN (not mounted)

RMAN> 2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=58 device type=DISK

allocated channel: a1
channel a1: SID=30 device type=DISK

released channel: t1
released channel: a1

RMAN>

Recovery Manager complete.

The connectivity seems successful. I am proceeding on actual restore now.

Step 5   Duplicate the database.

restore.sh





















Start running the above script in background mode.

[oracle@RACTEST1 usben]$ nohup ./restore.sh &
[1] 15411
[oracle@RACTEST1 usben]$

Tailing the log file while duplicate the database.
tail -f  restore.log


Here is the complete restore log file.
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 8 16:39:09 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN>
connected to target database: USBEN (DBID=2379284599)

RMAN>
connected to auxiliary database: USBEN (not mounted)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=43 device type=DISK

allocated channel: a1
channel a1: SID=33 device type=DISK

Starting Duplicate Db at 08-FEB-16
current log archived

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     281018368 bytes

Fixed Size                     2923440 bytes
Variable Size                222299216 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes
allocated channel: a1
channel a1: SID=33 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/USBEN/CONTROLFILE/current.304.903285577'', ''+DATA/USBEN/CONTROLFILE/current.294.903285577'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''USBEN'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''USBEN'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'usben' primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/USBEN/CONTROLFILE/current.304.903285577'', ''+DATA/USBEN/CONTROLFILE/current.294.903285577'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''USBEN'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''USBEN'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     281018368 bytes

Fixed Size                     2923440 bytes
Variable Size                222299216 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes
allocated channel: a1
channel a1: SID=33 device type=DISK

Starting restore at 08-FEB-16

channel a1: starting datafile backup set restore
channel a1: using network backup set from service usben
channel a1: restoring control file
channel a1: restore complete, elapsed time: 00:00:04
output file name=+DATA/USBEN/CONTROLFILE/current.304.903285577
output file name=+DATA/USBEN/CONTROLFILE/current.294.903285577
Finished restore at 08-FEB-16

database mounted

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   restore
   from service  'usben'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-FEB-16

channel a1: starting datafile backup set restore
channel a1: using network backup set from service usben
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to +DATA
channel a1: restore complete, elapsed time: 00:03:05
channel a1: starting datafile backup set restore
-- trimming the info here for brevity
channel a1: starting datafile backup set restore
channel a1: using network backup set from service usben
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00007 to +DATA
channel a1: restore complete, elapsed time: 00:00:03
Finished restore at 08-FEB-16

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'usben'
           archivelog from scn  2241689;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 08-FEB-16

channel a1: starting archived log restore to default destination
channel a1: using network backup set from service usben
channel a1: restoring archived log
archived log thread=1 sequence=55
channel a1: restore complete, elapsed time: 00:00:01
channel a1: starting archived log restore to default destination
channel a1: using network backup set from service usben
channel a1: restoring archived log
archived log thread=1 sequence=56
channel a1: restore complete, elapsed time: 00:00:01
Finished restore at 08-FEB-16

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=903285999 file name=+DATA/USBEN/DATAFILE/system.309.903285615
-- trimming the info here for brevity
input datafile copy RECID=14 STAMP=903286000 file name=+DATA/USBEN/DATAFILE/usben_indx.296.903285995

contents of Memory Script:
{
   set until scn  2242137;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 08-FEB-16

starting media recovery

archived log for thread 1 with sequence 55 is already on disk as file /backup/usben/arch/1_55_902782329.dbf
archived log for thread 1 with sequence 56 is already on disk as file /backup/usben/arch/1_56_902782329.dbf
archived log file name=/backup/usben/arch/1_55_902782329.dbf thread=1 sequence=55
archived log file name=/backup/usben/arch/1_56_902782329.dbf thread=1 sequence=56
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-FEB-16
Oracle instance started

Total System Global Area     281018368 bytes

Fixed Size                     2923440 bytes
Variable Size                222299216 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''USBEN'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''USBEN'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Executing: create pfile='/ora/app/oracle/product/12.1.0.1/db_1/dbs/initusben.ora' from spfile
Oracle instance started

Total System Global Area     281018368 bytes

Fixed Size                     2923440 bytes
Variable Size                222299216 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "USBEN" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+DATA', '+DATA' ) SIZE 200 M ,
  GROUP   2 ( '+DATA', '+DATA' ) SIZE 200 M
 DATAFILE
  '+DATA/USBEN/DATAFILE/system.309.903285615'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for clone tempfile  2 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/USBEN/DATAFILE/usben_data.306.903285801",
 "+DATA/USBEN/DATAFILE/sysaux.302.903285803",
 "+DATA/USBEN/DATAFILE/undotbs1.297.903285909",
 "+DATA/USBEN/DATAFILE/example.299.903285917",
 "+DATA/USBEN/DATAFILE/users.307.903285991",
 "+DATA/USBEN/DATAFILE/usben_indx.296.903285995";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 2 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/usben_data.306.903285801 RECID=1 STAMP=903286025
cataloged datafile copy
-- trimming the info here for brevity
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/usben_indx.296.903285995 RECID=6 STAMP=903286026

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=903286025 file name=+DATA/USBEN/DATAFILE/usben_data.306.903285801
-- trimming the info here for brevity
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=903286026 file name=+DATA/USBEN/DATAFILE/usben_indx.296.903285995

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 08-FEB-16

released channel: t1

RMAN>

Recovery Manager complete.

Step 6 Create the spfile in ASM disk.  We could do this step at the beginning too.  However, i do this step at the end for my convenience. 


sys@usben> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
-- Creating spfile in ASM disk
sys@usben> create spfile='+DATA/USBEN/PARAMETERFILE/spfileusben.ora' from PFILE;

File created.

sys@usben> !

-- Here i updated the parameter file to point to SPFILE in ASM disk.

[oracle@RACTEST1 dbs]$ cat initusben.ora
SPFILE='+DATA/USBEN/PARAMETERFILE/spfileusben.ora'
[oracle@RACTEST1 dbs]$
-- Restart the database with spfile.
sys@usben> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@usben> startup
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  2923440 bytes
Variable Size             222299216 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
Database mounted.
Database opened.
sys@usben> set echo on
sys@usben> set feedback on
sys@usben> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/USBEN/PARAMETERFILE/spfileusben.ora

sys@usben>

Step 7  Verify the restored database.




















The database restored successfully and migrated to ASM instance.

Comments

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

Register Archive log file manually in standby Database

How to check UNDO tablespace usage and who is using more undo